SQLTeam.com | Weblogs | Forums

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?


#6

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