Using if in a mysql query

Question

I already have this query:

cursor.execute("SELECT calldate, dst, billsec, accountcode, disposition, 
                       (billsec/60*%s) as total 
                FROM   cdr 
                WHERE  calldate >= '%s' and calldate < '%s' 
                   and disposition like '%s' and accountcode = '%s' 
                   and dst like '%s'" %(rate, start_date, end_date, status, accountcode, destino)
              )

Obs: dst is a telephone number.

But now what I need to do is: If the 5th char of dst is <= 5, then billsec/60*0.11 as total else billsec/60*0.16.

Is it possible?

This way I got an error in mysql syntax:

    cursor.execute("""SELECT calldate, dst, billsec, accountcode, disposition, 
                    case when cast(substring(dst,4,1), unsigned) <= 5 then
                            billsec/60*%s as total
                    else
                            billsec/60*%s as total
                    end case
                    FROM cdr where calldate >= '%s' and calldate < '%s' and disposition like '%s' and accountcode = '%s' and dst like '%s'""" %(rate_fixo, rate_movel, start_date, end_date, status, accountcode, destino))

Piotr Wadas · Accepted Answer · 2012-09-15 23:58:24Z

With SELECT you actually specify set of columns to be returned. This set can be simply names of columns, or specific transformation of these columns, calling SQL function. "IF" actually is more related to procedural SQL, in your example you need something called CASE expression. The trick is to define a column in result set by CASE expression, like this

SELECT acol, bcol, ccol, dcol from t1 where ...

compare with

SELECT acol, CASE WHEN sqlfunc_like_substr(someparams,bcol) THEN bcol ELSE some_other_way_modified(bcol) END, ccol, dcol from t1 WHERE ...

Don't remember CASE exact syntax at the moment, but this is the way. More, you can name resulting column eg.

SELECT acol as uuu, CASE WHEN ... END as mmm, ccol as nnn, dcol as qqq FROM t1 where...

and so on :) The point is to understand, that SELECT actually does not selects columns to be retrieved, but defines particular set of result columns, with some of them as they are in table, and some as subresult of column value transformation or a string, or NULL, or sth else.

Hans Then · Accepted Answer · 2012-09-16 09:38:09Z

2

Yes this is possible. Mysql queries can contain if statements, only they are called case statements, as has been noted by other Piotr Wadas.

See here: http://dev.mysql.com/doc/refman/5.0/en/case.html

To extract a character from a string use substring. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

So the complete solution would be something like:

case when cast(substring(dst,4,1), unsigned) <= 5 then 
   billsec/60*0.11
else
   billsec/60*0.16
end case

edited Sep 16, 2012 at 9:38

answered Sep 15, 2012 at 23:55

Hans Then

11.4k3 gold badges36 silver badges52 bronze badges

Collectives™ on Stack Overflow

Using if in a mysql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related