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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy