How to make if else Condition in Adv. SQL Query?

I want to retrieve HomeZipCode From Customers but condition is that if value of HomeZipCode is 0 then Store 'Hello' in Output otherwise Store actual value of HomeZipCode  from Customers Entity.

Note: Output will be stored in HomeZipCode Structure attribute of type Text and in Customers Entity HomeZipCode is of Type Integer.

I tried by following query but it showing query error after running query->

(CASE WHEN {Customers}.[HomeZipCode]=0 THEN ('Hello') ELSE ({Customers}.[HomeZipCode]) END) as HomeZipCode
mvp_badge
MVP
Solution

Hello Roshan,

The CASE condition would be a good choice for your requirement. Regarding your note, you don't have to worry about data types if your HomeZipCode output structure has an attribute of type Text - if you try to store an Integer in it, it will be implicitly converted to a string.

I don't know what error you're receiving, but I suspect the issue is that a CASE can only return one datatype (or multiple datatypes if they can be implicitly converted). So this will return an error, because it will return a string or an integer:

SELECT CASE WHEN 0 = 0 THEN 'Hello' ELSE 1 END as HomeZipCode

but this will not, since it only returns strings:

SELECT CASE WHEN 0 = 0 THEN 'Hello' ELSE '1' END as HomeZipCode

All you need to do is convert or cast your HomeZipCode into a String in the CASE branch, something like this:

CASE WHEN {Customers}.[HomeZipCode]=0 THEN 'Hello' ELSE CONVERT(varchar, {Customers}.[HomeZipCode]) END as HomeZipCode

and since your CASE now only returns strings, you shouldn't receive any errors.

Hello Afosno,

Now I understand the actual problem with the statement.

Thank You to Help Me in Understandable manner.

Thanks Again.

mvp_badge
MVP

No problem Roshan, glad to be of assistance.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.