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.