Error converting data type varchar to numeric.

Question

Error converting data type varchar to numeric.

night_devil_460

SSC Veteran

Points: 290
More actions
February 12, 2009 at 3:36 am

#130958

hello... i need help.. im trying UNION ALL 3 selects but i have error.
my code:
______________________
SELECT MAX(CASE WHEN obrano = 1 THEN nome ELSE '' END) AS Forn1,
MAX(CASE WHEN obrano = 2 THEN nome ELSE '' END) AS Forn2,
MAX(CASE WHEN obrano = 3 THEN nome ELSE '' END) AS Forn3
FROM (
SELECT LEFT(nome, 11) AS nome,
obrano
FROM bo
WHERE dataobra >= '20090101'
AND dataobra < '20100101'
AND ndos = 13
AND obrano BETWEEN 1 AND 3
) AS d
UNION ALL
SELECT bi.ref,
bi.design,
bi.qtt
FROM bi WITH (NOLOCK)
INNER JOIN (
SELECT bostamp
FROM bo WITH (NOLOCK)
WHERE dataobra >= '20090101'
AND dataobra < '20100101'
AND ndos = 13
AND obrano BETWEEN 1 AND 3
) AS d ON d.bostamp = bi.bostamp
UNION ALL
SELECT bi.edebito,
bi.DESCONTO,
bi.DESC2
FROM bi WITH (NOLOCK)
INNER JOIN (
SELECT bostamp
FROM bo WITH (NOLOCK)
WHERE dataobra >= '20090101'
AND dataobra < '20100101'
AND ndos = 13
AND obrano BETWEEN 1 AND 3
) AS d ON d.bostamp = bi.bostamp
________________________________--
my error is:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
can help me?? thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

If your statements run fine taken one at a time, the problem is in the UNION. You have to ensure that the data type is the same for each column of your three statements. If the first statements selects a numeric value for the first column and the second statement selects a varchar value for the same column, when you try to UNION the statements, the engine tries to convert to numeric, that could lead to conversion errors.

Try converting explicitly and separately to numeric or varchar.

-- Gianluca Sartori

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Excerpt from BOL:

Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied.

So, if the individual statements runs fine, then the problem is with the UNION ALL. So, check the data types of the individual statements and convert each of them to the appropriate data types.

--Ramesh

night_devil_460 SSC Veteran Points: 290 More actions · Answer 3

Gianluca Sartori (2/12/2009)
If your statements run fine taken one at a time, the problem is in the UNION. You have to ensure that the data type is the same for each column of your three statements. If the first statements selects a numeric value for the first column and the second statement selects a varchar value for the same column, when you try to UNION the statements, the engine tries to convert to numeric, that could lead to conversion errors.
Try converting explicitly and separately to numeric or varchar.

thanks... thats it... it works now..

thanks