CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 1128

SQL: Ternary operations

In C++, you can do this:

T.x = (T.y > 1 && (T.x - T.y < 0)) ? 0 : (T.x - T.y)

which in [almost] plain english, is

if T.y > 1 and T.x-T.y < 0 then
    set T.x to 0
else 
    set T.x to T.x-T.y

Is it possible to do the same thing using just SQL, without using stored procs or triggers?

Upvotes: 9

Views: 6253

Answers (4)

onedaywhen
onedaywhen

Reputation: 57063

Simplified:

SELECT *, 
      CASE WHEN (y > 1 AND x < y) THEN 0 ELSE (x - y) END AS result
 FROM T;

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

Use the CASE statement:

CASE WHEN T.y > 1 AND (T.x - T.y) < 0 THEN 0 ELSE (T.x - T.y) END

Upvotes: 11

CloudyMarble
CloudyMarble

Reputation: 37576

Yes its possible, take a look at the documentation, it says:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3.

This untested code should be your case: 

SELECT IF(((T.y > 1) and (T.x-T.y < 0)), 0, (T.x-T.y))

Upvotes: 5

Uday Sawant
Uday Sawant

Reputation: 5798

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

Check this manual for details control-flow-functions

Upvotes: 0

Related Questions