samedi 2 janvier 2016

How to write condition if the column is not null in mysql

enter image description here

I need to eliminate the rows, if the enrollDate and cancelDate is same. So i wrote the query as follows SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a.loanEndDate, '%Y-%m-%d' ) >= STR_TO_DATE( '2015-12-31', '%Y-%m-%d' ) AND FIND_IN_SET( a.status, 'A,C' ) AND STR_TO_DATE( a.enrollDate, '%Y-%m-%d' ) <> STR_TO_DATE( a.cancelDate, '%Y-%m-%d' )

But if cancelDate is Null, it didn't display as different.(Only need to eliminate the id=8551 in the above figure. This is my problem) So i wrote another query using if condition

SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a.`loanEndDate` ,  '%Y-%m-%d' ) >= STR_TO_DATE(  '2015-12-31',  '%Y-%m-%d' ) AND FIND_IN_SET( a.status,  'A,C' ) AND (CASE WHEN a.cancelDate IS NOT NULL THEN (STR_TO_DATE( a.`enrollDate` ,  '%Y-%m-%d' ) <> STR_TO_DATE(  a.cancelDAte,  '%Y-%m-%d' )) END  ) 

Its also fails.Please guide me to fix this

Aucun commentaire:

Enregistrer un commentaire