CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 729

MySQL COALESCE Function failing to return non null value

I have this very simply MySQL query:

SELECT m.REF_DATA_ID, COALESCE(m.SQL_FUNCTION, m.PHP_FUNCTION ) AS FUNCTION FROM AAB_REFERENCE_DATA_MANAGER m;

which returns the below data:

REF_DATA_ID | FUNCTION
88295       |
88296       |
88297       |
88298       | now()
88299       |
88300       | now()
88301       |

COALESCE() does not seem to be functioning properly in my query for some reason, when I run the below query:

SELECT m.REF_DATA_ID, m.SQL_FUNCTION, m.PHP_FUNCTION FROM AAB_REFERENCE_DATA_MANAGER m;

returns:

REF_DATA_ID  SQL_FUNCTION  PHP_FUNCTION
88295       |             |
88296       |             |
88297       |             |
88298       | now()       |
88299       |             | get_session_user_id
88300       | now()       |
88301       |             | get_session_user_id

I'm just not sure what I'm doing wrong with COALESCE here...

The output I'm looking for is:

REF_DATA_ID  FUNCTION
88295       |        
88296       |        
88297       |        
88298       | now()  
88299       | get_session_user_id
88300       | now()
88301       | get_session_user_id

Upvotes: 0

Views: 80

Answers (2)

H.K
H.K

Reputation: 183

   SELECT m.REF_DATA_ID, 
          COALESCE(If(Length(m.SQL_FUNCTION)=0, NULL, m.SQL_FUNCTION),
                   If(Length(m.PHP_FUNCTION)=0, NULL, m.PHP_FUNCTION)) AS FUNCTION
   FROM AAB_REFERENCE_DATA_MANAGER m;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

A blank string is not NULL (except in Oracle). So, be more explicit with the comparison:

SELECT m.REF_DATA_ID,
       (CASE WHEN m.SQL_FUNCTION IS NULL OR
                  TRIM(m.SQL_FUNCTION) = ''
             THEN m.PHP_FUNCTION
             ELSE m.SQL_FUNCTION
        END) AS FUNCTION
FROM AAB_REFERENCE_DATA_MANAGER m;

Upvotes: 1

Related Questions