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
– klutt

2017-03-21 09:23:49 +00:00
Commented Mar 21, 2017 at 9:23 — klutt
– klutt, Commented Mar 21, 2017 at 9:23

Giorgos Betsos · Accepted Answer · 2017-03-21 09:23:37Z

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, 2017 at 9:23

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AI. Over a year ago

If I want to do the update query when the cut_off_dt is null, I can use this method too ?

Giorgos Betsos Over a year ago

@AI. Yes you can

beejm · Accepted Answer · 2017-03-21 09:25:30Z

1

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'

answered Mar 21, 2017 at 9:25

beejm

2,4811 gold badge13 silver badges19 bronze badges

Comments

baao · Accepted Answer · 2017-03-21 09:25:48Z

1

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';

answered Mar 21, 2017 at 9:25

baao

73.5k18 gold badges152 silver badges209 bronze badges

1 Comment

AI. Over a year ago

Do you have any idea for this ? stackoverflow.com/questions/42931666/…

Collectives™ on Stack Overflow

Write IF..ELSE in MYSQL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related