October 19, 2015 at 9:16 am
Hi guys,
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
Can you please provide some insight?
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
October 19, 2015 at 9:25 am
One of the data points must not be a number or it's a number greater than a tinyint can handle (255 or 256--can't remember exactly which).
October 19, 2015 at 9:27 am
It'll be your WHERE clause. If you have any non-numeric values in any of those four columns, you'll get the error. Try this instead for each of the columns:
...
WHERE t1.Code1TypeId = '20'
...
John
Edit
Forget that, and try this:
select
CAST(t1.Code1TypeId AS char(2)) + ';' +
CAST(t1.Code2TypeId AS char(2)) + ';' +
CAST(t1.Code3TypeId AS char(2)) + ';' +
CAST(t1.Code4TypeId AS char(2)) as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
October 19, 2015 at 9:33 am
Good catch. It is probably trying to convert the semicolons to an int, which of course isn't possible.
October 19, 2015 at 9:40 am
John Mitchell-245523 (10/19/2015)
It'll be your WHERE clause. If you have any non-numeric values in any of those four columns, you'll get the error. Try this instead for each of the columns:
...WHERE t1.Code1TypeId = '20'
...
John
Edit
Forget that, and try this:
selectCAST(t1.Code1TypeId AS char(2)) + ';' +
CAST(t1.Code2TypeId AS char(2)) + ';' +
CAST(t1.Code3TypeId AS char(2)) + ';' +
CAST(t1.Code4TypeId AS char(2)) as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
John Mitchell-245523, thanks for your help, you resolved my issue. You're Awesome! Ronkyle, thanks for chiming in also.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply