RE: Help with CASE WHEN date is >= 90
I am trying to do a CASE WHEN (first time) and I am having the error field not found. This is in a saved search. I think my error might be in the packedDate or transDate comparing to 90. Would really love any help here as this is my first delve into SQL. Thanks
CASE
WHEN {quantityBackOrdered} IS NULL THEN '<div style="color:black;background-color:yellow;padding:5px;text-align:center;border-radius: 25px;">' || '☆ No Back Order' || '</div>'
WHEN {packedDate} >= 90 THEN '<div style="color:black;background-color:blue;padding:5px;text-align:center;border-radius: 25px;">' || '☆ No Pack' || '</div>'
WHEN {tranDate} >= 90 THEN '<div style="color:black;background-color:red;padding:5px;text-align:center;border-radius: 25px;">' || '☆ No Trans' || '</div>'
WHEN {packedDate} >= 90 AND {tranDate} >= 90 THEN '<div style="color:black;background-color:#C2A887;padding:5px;text-align:center;border-radius: 25px;">' || '☆ DUSTY' || '</div>'
ELSE '<div style="color:white;background-color:green;padding:5px;text-align:center;border-radius: 25px">' || '★ Great'
END
Would really love any help here as this is my first delve into SQL. Thanks
The packeddate and trandate is going to be a date field in DD/MM/YYYY format. Are you actually trying to say if the packed date was more than 90days ago then apply this formating? If so you need to use the following
case when trunc({today})-{packedDate} > 90 then …
