Help with a SQL Union query please. Conversion errors!

Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.

The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."

This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it?

The union query looks like this:

exec('
select

RecordNumber,
Sum(rank1) as HbA1CRank1,
max(Operator1) as HbA1COperator1,
sum(contentValue1) as HbA1CContentvalue1,
max(dos1) as HbA1CDOS1,
Sum(rank2) as HbA1CRank2,
max(Operator2) as HbA1COperator2,
sum(contentValue2) as HbA1CContentvalue2,
max(dos2) as HbA1CDOS2,
Sum(rank3) as HbA1CRank3,
max(Operator3) as HbA1COperator3,
sum(contentValue3) as HbA1CContentvalue3,
max(dos3) as HbA1CDOS3

from

(SELECT DISTINCT
TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2,
0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 0)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,
0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 1)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3,
contentvalue AS contentvalue3, DOS AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 2)
ORDER BY RecordNumber )tblHgA1C

group by RecordNumber

')
GO

Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up =\

Thank you!

The error most likely comes from having "cast(Operator as varchar) as Operator1" in the first query and then "0 as Operator1" in the subsequent ones. It should be "'' as Operator1"

However,write your query like this instead, and you won't have to use a UNION at all

select

RecordNumber,
Sum(CASE WHEN Rank=0 THEN Rank ELSE NULL END) as HbA1CRank1, -- WILL BE 0
max(CASE WHEN Rank=0 THEN Operator ELSE NULL END) as HbA1COperator1,
sum(CASE WHEN Rank=0 THEN contentValue ELSE NULL END) as HbA1CContentvalue1,
max(CASE WHEN Rank=0 THEN DOS ELSE NULL END) as HbA1CDOS1,
Sum(CASE WHEN Rank=1 THEN Rank ELSE NULL END) as HbA1CRank2,
max(CASE WHEN Rank=1 THEN Operator ELSE NULL END) as HbA1COperator2,
sum(CASE WHEN Rank=1 THEN contentValue ELSE NULL END) as HbA1CContentvalue2,
max(CASE WHEN Rank=1 THEN DOS ELSE NULL END) as HbA1CDOS2,
Sum(CASE WHEN Rank=2 THEN Rank ELSE NULL END) as HbA1CRank3,
max(CASE WHEN Rank=2 THEN Operator ELSE NULL END) as HbA1COperator3,
sum(CASE WHEN Rank=2 THEN contentValue ELSE NULL END) as HbA1CContentvalue3,
max(CASE WHEN Rank=2 THEN DOS ELSE NULL END) as HbA1CDOS3

from ...|||

Gunteman! Oh man, thank you so much! I can't believe how easy that was, your solution worked perfectly. That's awesome, you made my night!

Thanks again =)

|||

Great! BTW, why did you use EXEC?

|||

about a year ago someone here recommended wrapping my union statements in the EXEC command when running them within stored procedures. I didnt' bother researching further... it seemed to work, so I just ran with it and kept doing it :)

Should I not bother with it?

sql