CODEWITHSUNDEEP
pythonmysqlsql
Leandro Lorenzini de Oliveira
Leandro Lorenzini de Oliveira

Reputation: 472

Using if in a mysql query

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))

Upvotes: 3

Views: 176

Answers (2)

Hans Then
Hans Then

Reputation: 11322

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

Upvotes: 2

Piotr Wadas
Piotr Wadas

Reputation: 1894

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.

Upvotes: 2

Related Questions