Menu
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 Can anybody help me? Thanks in advance
227
The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END . For the first syntax, case_value is an expression.
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.
To search for column values that are NULL , you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression: mysql> SELECT * FROM my_table WHERE phone = NULL; To look for NULL values, you must use the IS NULL test.
ISNULL() Function, COALESCE() Function And CASE Statement There are three ways in which we can remove Null values in Table data and show them to the user. In our Table data, some columns contain null values. We can replace it using ISNULL() function , COALESCE() function, and CASE Statement.
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 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'.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
