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

Rookie Asked on August 9, 2021 in Saved Searches.
Add Comment
1 Answer(s)

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 …

Rookie Answered on August 9, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor