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

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.


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.