Write IF..ELSE in MYSQL

Question

I have a table looked like this

mysql> select * from rc;

    +----+----------------------------+----------------------------+----------------------------+
    | id | business_date              | cutoff_dt                  | recon_dt                   |
    +----+----------------------------+----------------------------+----------------------------+
    |  1 | 2017-03-21 16:50:29.032000 | 2017-03-21 16:50:31.441000 | 2017-03-21 16:50:32.832000 |
    +----+----------------------------+----------------------------+----------------------------+
    1 row in set (0.00 sec)

I want to run query

-Select * from rc where business_date = '2017-03-17' - ifcutoff_dt` is null or empty, it will display null, otherwise display not null

I wrote it into shell script.

#! /bin/bash
mysql -u root -p <<rcard
use rcard;
SELECT * 
(CASE WHEN (cut_off_dt = "NULL")
    THEN
      echo "Null"
    ELSE
       echo "NOT NULL"
END)
from rc WHERE business_date = '2017-03-17';
rcard

But I get error

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CASE WHEN (cut_off_dt = "NULL")
    THEN
      echo "Null"
    ELSE
   ' at line 2

Is it the correct way to write the IF ELSE in MySQL ?

Ubuntu tag? This has nothing to do with Ubuntu. – klutt Mar 21 '17 at 09:23 — klutt, Mar 21 '17 at 09:23

score 2 · Accepted Answer · answered Mar 21 '17 at 09:23

2

This is not the correct way to check for NULL values.

Try this:

SELECT *,
       IF(mepslog_cut_off_dt IS NULL, 'NULL', 'NOT NULL')  
from rc_mepslog 
WHERE mepslog_business_date = '2017-03-17';

You can alternatively use a CASE expression:

SELECT *,
       CASE 
          WHEN mepslog_cut_off_dt IS NULL THEN 'NULL'
          ELSE 'NOT NULL'
       END
from rc_mepslog 
WHERE mepslog_business_date = '2017-03-17';

answered Mar 21 '17 at 09:23

Giorgos Betsos

71,379
9
63
98

If I want to do the update query when the `cut_off_dt` is null, I can use this method too ? – AI. Mar 21 '17 at 09:25
@AI. Yes you can – Giorgos Betsos Mar 21 '17 at 09:27

score 1 · Answer 2 · answered Mar 21 '17 at 09:25

SELECT *, (CASE WHEN (mepslog_cut_off_dt IS NULL)
    THEN
      'Null'
    ELSE
       'NOT NULL' END) from rc WHERE business_date = '2017-03-17'

To compare if the value is null, use column IS NULL instead of column = 'NULL'; the latter would check if the column you are comparing has the string 'null'

score 1 · Answer 3 · answered Mar 21 '17 at 09:25

There's a missing comma after select * and none of your table names you specified in the query exists. To separate a table from the column, you use a dot .. There are other wrong written column names appart from that. Also, you don't use echo, you simply specify what to do. Also, I'd suggest to use an alias for the selected column from the case (AS 'nullOrNotNull')

SELECT * ,
CASE WHEN cut_off_dt IS NULL
    THEN
      'Null'
    ELSE
      'NOT NULL'
END AS 'nullOrNotNull'
from rc WHERE business_date = '2017-03-17';

Do you have any idea for this ? http://stackoverflow.com/questions/42931666/update-query-wrong-in-mysql — AI., Mar 21 '17 at 15:54

Write IF..ELSE in MYSQL

3 Answers3