CODEWITHSUNDEEP
mysqlsql
user1023242
user1023242

Reputation: 503

MYSQL Case in select statement for checking null

in MySQL query if I pass:

  case guides.Gud_Publish_Date 
     when null then "Unscheduled" 
     else "Forth Coming Titles" 
  end

then it is considering all is null even the Gud_Publish_Date has value also. the full SQL statement is

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE guides.Gud_Publish_Date
            WHEN NULL
                THEN "Unscheduled"
            ELSE "Forth Coming Titles"
            END
        ) AS Schedules
FROM guides

Upvotes: 47

Views: 136587

Answers (4)

Diego-MX
Diego-MX

Reputation: 2349

I found this -a couple of months- old post. Using COALESCE option as intended by Rajan, you can do, 

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , CASE COALESCE(guides.Gud_Publish_Date, 0)
          WHEN 0 THEN 'Unscheduled'
                 ELSE 'Forth Coming Titles'
          END  AS Schedules
FROM guides

The code above assumes that guides.Gud_Publish_Date cannot take the value 0, which I can do because it is a date. If that weren't the case you can change 0 for another value that it cannot take; maybe your favorite float like 3.1415 or a null identifier 'null'.

Upvotes: 15

Rajan
Rajan

Reputation: 1511

Try this

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , coalesce((
        CASE guides.Gud_Publish_Date
            WHEN NULL
                THEN 'Unscheduled'
            ELSE 'Forth Coming Titles'
            END
        ), 'Unscheduled') AS Schedules
FROM guides

Upvotes: -2

John Woo
John Woo

Reputation: 263803

try using IF

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , IF(guides.Gud_Publish_Date IS NULL,'Unscheduled','Forth Coming Titles') 
             AS Schedules
FROM guides

or if you really want CASE

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE 
            WHEN guides.Gud_Publish_Date IS NULL
            THEN 'Unscheduled'
            ELSE 'Forth Coming Titles'
        END
      ) AS Schedules
FROM guides

Upvotes: 90

Vardan Gupta
Vardan Gupta

Reputation: 3585

SELECT guides.Gud_Id
    , guides.Gud_Image
    , guides.Gud_SubEditor
    , guides.Gud_Reprint_Status
    , guides.Gud_Publish_Date
    , guides.Gud_Img_Chk
    , guides.Gud_Published
    , guides.Gud_View
    , (
        CASE WHEN guides.Gud_Publish_Date IS NULL
            THEN "Unscheduled"
            ELSE "Forth Coming Titles"
            END
        ) AS Schedules
FROM guides

Upvotes: 7

Related Questions