Hi Experts,
Please may I know how to fix this?
I tired this
COALESCE( R.VendorName, T.ContractorName, 0) ContractorName - Did not work
Error: Conversion failed when converting the varchar value 'Michigan powerclean Pvt Ltd' to data type int.
So I tried this ..
COALESCE(CAST(R.VendorName AS varchar(100)), CAST(T.ContractorName AS varchar(100)),0) ContractorName
Error: Conversion failed when converting the varchar value 'Michigan powerclean Pvt Ltd' to data type int.
Regards
March 10, 2022 at 7:04 am
The problem is here:
COALESCE( R.VendorName, T.ContractorName, 0)
What is the idea behind that zero in there?
_____________
Code for TallyGenerator
COALESCE is a shorthand CASE expression, and a CASE expression uses data type precedence to determine the data type for the returned value. With column names like VendorName and ContractorName these are clearly string based data types, but 0 is a int; which has a higher data type precedence than any of the string based data types. As such your COALESCE will attempt to implicitly convert the value of R.VendorName or T.ContractorName to an int, and unsurprisingly this fails.
If you, for some reason, want to return a 0 when both of the columns are NULL then make sure that value is a string based value ('0'). Honestly, though, I agree with Sergiy; returning 0 for a "name" doesn't make any sense.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 11, 2022 at 7:45 am
Thank you both very much.
I thought I typed my thanks to @sergiy, I tried to re-use the existing code with changes and I messed it up 🙁
Yes the one is used had a two values and if basically meant if none of them, then value is int 0 and not having full understanding of the function re-used it and changed the parameters I needed and used CAST based on error but didn't think well.
Thank you both a ton.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply