Problems with a case statement

1

Hello Everybody,

I need to convert the following piece of code from SAS into an SQL case statement.
CASE WHEN (INDEX(UPCASE(SALES_STATUS), 'FORM RECEIVED')) >= 1 THEN 'Form Received'

I am struggling with the functions and the syntax and I am quite new to SQL.

Any help would be much appreciated

Kind Regards

Vinnie

2 
CASE 
	WHEN (CHARINDEX(UPPER('FORM RECEIVED'), SALES_STATUS)) >= 1 THEN 'Form Received'
	ELSE 'Form not received' -- this line is optional
END
3

Hi James

Thank you so much for your help. This works perfectly.

Regards

Vinnie

4

Actually, it is not quite correct. It worked probably because your database has case-insensitive collations, or because the sales_status column had all upper case. Really it should be like this:

CASE 
	WHEN (CHARINDEX('FORM RECEIVED', UPPER(SALES_STATUS))) >= 1 THEN 'Form Received'
	ELSE 'Form not received' -- this line is optional
END
5

To add to this we had a situation where were relying on some text upstream and it changed on us and broke stuff.

I like using enums to represent status
Form received could be 1 etc
Have a status table for these.

My 2 cents. Then no string parsing and no case checking?

1 Like
6

Thanks again James I have now amended as per your post.