CODEWITHSUNDEEP
mysqlif-statement
nagoor meeran
nagoor meeran

Reputation: 75

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

Upvotes: 0

Views: 30

Answers (1)

Stalinko
Stalinko

Reputation: 3646

Try something like that:

SELECT a.id, a.clientId, a.enrollDate, a.cancelDate 
FROM gap_borrower a WHERE 
    DATE(a.loanEndDate) >= DATE('2015-12-31') 
    AND FIND_IN_SET( a.status,  'A,C' ) 
    AND (DATE(a.enrollDate) <> DATE(a.cancelDate) OR a.enrollDate IS NULL OR a.cancelDate IS NULL)
    --uncomment this if you don't need rows where  the both dates are null
    --AND (a.enrollDate IS NOT NULL or a.cancelDate IS NOT NULL)

Upvotes: 1

Related Questions