CODEWITHSUNDEEP
mysqlcase
Quark
Quark

Reputation: 59

CASE expression in MySQL not evaluating

I have a CASE expression in one of MySQL queries that checks for the lesser or earlier of the two dates, however I am having problem getting it to work properly

update SaveContactForm7_1
SET `TStat` = 
CASE
    WHEN 
        (`DateP1` IS NOT NULL OR `DateP2` IS NOT NULL) 
        AND `T1A` BETWEEN (`Date`-1 AND (`DateP1` OR `DateP2`)) 
        AND `T1A` < `SLH` 
        AND `T2A` IS NULL
        AND `T3A` IS NULL 
    THEN 'T1'
    WHEN
       (`DateP1` IS NOT NULL OR `DateP2` IS NOT NULL) 
        AND `T2A` BETWEEN (`Date`-1 AND (`DateP1` OR `DateP2`)) 
         AND `T2A` < `SLH` 
        AND `T3A` IS NULL 
    THEN 'T2'
END;

It always to evaluates to T1 or T2 or skips the T1A < SLH evaluation altogether. I have tried placing in braces (T1A<SLH) but that doesn't work either. 

select * from SaveContactForm7_1 where `T1A`>`SLH` and TStat='T1';

and it shows all the rows that "escaped" the update query. DateP1, DateP2, Date, T1A, T2A... are all date types. How can I write the update query so that the T1A < SLH is evaluated. Can you please advise?

Upvotes: 0

Views: 72

Answers (1)

GMB
GMB

Reputation: 222462

This will not do what you expect :

AND `T1A` BETWEEN (`Date`-1 AND (`DateP1` OR `DateP2`))

I guess that you want this instead :

AND `T1A` BETWEEN (`Date`-1 AND COALESCE(`DateP1` `DateP2`))

Upvotes: 1

Related Questions