Aleks
asked on
Syntax error in SQL
I have the following SQL
contact_id_interntal is an INT, so I am getting an error. I imagine I need to convert it into varchar. How can I do this?
Below is the error:
Help is appreciated.
SELECT a.UserId, 'contact' AS UserType, + 'Contact |' + ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + a.contact_id_interntal + ')' AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'
contact_id_interntal is an INT, so I am getting an error. I imagine I need to convert it into varchar. How can I do this?
Below is the error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ')' to data type int.
Help is appreciated.
I think it might be the first plus sign (before 'Contact |') in your select query. That might be confusing the query parser, thinking it might be a number since it's starting with a plus sign.
Also, are you sure the the + operator is the string concatenator in the database you're using?
SELECT a.UserId, 'contact' AS UserType, 'Contact |' + ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + a.contact_id_interntal + ')' AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'
Also, are you sure the the + operator is the string concatenator in the database you're using?
ASKER
Neither queries worked. If I change the 'a.contact_id_interntal ' for a varchar field it works fine.
I think I need to convert that integer field to varchar to avoid the issue.
So far the error was the same.
I think I need to convert that integer field to varchar to avoid the issue.
So far the error was the same.
Which database are you using? You might need a different operator for concatenating your strings. (It might not be a plus sign.)
>> If I change the 'a.contact_id_interntal ' for a varchar field it works fine
Did you try quoting the number?
a.contact_id_interntal = '50002'
Did you try quoting the number?
a.contact_id_interntal = '50002'
ASKER
sql 2012
ASKER
I did the issue is in the value being displayed
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>Conversion failed when converting the varchar value ')' to data type int.
I assume this is at the place
a.contact_id_interntal + ')'
Line 1 is reported, isn't it?
So, if a.contact_id_interntal is an integer column, you need CAST(a.contact_id_internta l as varchar(x))+')' at that place.
You could ask why the error message didn't already report '(' + a.contact_id_interntal. Well, it seems string+int would work, but not string+int+string.
Anyway, when you combine fields to an overall string it's best to cast or use other type conversion functions on any column not string/varchar itself, already. Don't depend on implicit type conversions.
Bye, Olaf.
I assume this is at the place
a.contact_id_interntal + ')'
Line 1 is reported, isn't it?
So, if a.contact_id_interntal is an integer column, you need CAST(a.contact_id_internta
You could ask why the error message didn't already report '(' + a.contact_id_interntal. Well, it seems string+int would work, but not string+int+string.
Anyway, when you combine fields to an overall string it's best to cast or use other type conversion functions on any column not string/varchar itself, already. Don't depend on implicit type conversions.
Bye, Olaf.
ASKER
Did some research and tested and as I though I had to convert it to varchar.
This worked
This worked
SELECT a.UserId, 'contact' AS UserType, 'Contact |' + ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + cast(a.contact_id_interntal as varchar(100)) + ')' AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'
Try this.
SELECT a.UserId, 'contact' AS UserType, + 'Contact |' + ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + CAST(a.contact_id_internal AS varchar) + ')' AS result
FROM dbo.Users AS a
WHERE (a.contact_id_internal = 50002) AND a.UserType = 'contact'
Note I changed contact_id_interntal to contact_id_internal as it looked like a typo, if it's not change it back.
SELECT a.UserId, 'contact' AS UserType, + 'Contact |' + ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + CAST(a.contact_id_internal
FROM dbo.Users AS a
WHERE (a.contact_id_internal = 50002) AND a.UserType = 'contact'
Note I changed contact_id_interntal to contact_id_internal as it looked like a typo, if it's not change it back.
Try getting rid of the leading "+" to the left of 'Contact |'
Open in new window