Unions

[stephm923]

I need to union the following query. I keep getting the error:error converting data type varchar to numeric.

The data types of each field are:
region varchar
carrier varchar
pcpname varchar
avgrisk numeric
effmonth smalldatetime
endmonth smalldatetime
mm int
hic nvarchar
prempay money
proj08 decimal
txtmemberid_phn nvarchar

Can you please take a look at this and let me know what I'm doing wrong. Thank you, Stephanie

SELECT year(effmonth) as effyear, REGION, CARRIER, PCPNAME, HIC, AVGRISK, AVG(PREMPAY) AS AVGPREM, ' ' as PROJ08, ' ' AS txtMemberID_PHN,
'RISKGRP' =
CASE
WHEN AVGRISK BETWEEN 0 AND .5 THEN 'RISK 0-.5'
WHEN AVGRISK BETWEEN .5 AND 1 THEN 'RISK 0.5-1'
WHEN AVGRISK BETWEEN 1 AND 2 THEN 'RISK 1-2'
WHEN AVGRISK BETWEEN 2 AND 3 THEN 'RISK 2-3'
WHEN AVGRISK BETWEEN 3 AND 4 THEN 'RISK 3-4'
ELSE 'RISK OVER 4'
END
--INTO #MEMBRISK
FROM #MEMBPREM
GROUP BY year(effmonth), REGION, CARRIER, PCPNAME, HIC, AVGRISK
UNION
SELECT ' ' as effyear, ' ' as REGION, ' ' as CARRIER, ' ' as PCPNAME, ' ' as HIC, 0 as AVGRISK, 0 AS AVGPREM, PROJ08, txtMemberID_PHN,
'PROJCAT' =
CASE
WHEN PROJ08 BETWEEN 0 AND .5 THEN 'RISK 0-.5'
WHEN PROJ08 BETWEEN .5 AND 1 THEN 'RISK 0.5-1'
WHEN PROJ08 BETWEEN 1 AND 2 THEN 'RISK 1-2'
WHEN PROJ08 BETWEEN 2 AND 3 THEN 'RISK 2-3'
WHEN PROJ08 BETWEEN 3 AND 4 THEN 'RISK 3-4'
WHEN PROJ08 IS NULL THEN 'NULL'
ELSE 'RISK OVER 4'
END
FROM #MEMBPREM
GROUP BY year(effmonth), REGION, CARRIER, PCPNAME, HIC, AVGRISK,PROJ08, txtMemberID_PHN

 

[Qik3Coder]

The database uses the columns from the first query to determine datatypes. you probably have to rtrim()/cast a number to a varchar().

your first query uses Proj08 as a '' (varchar) and you have proj08 as a decimal in the second query.

-Sometimes the answer to your question is the hack that works

 

[SQLDenis]

' ' as PROJ08

can not be converted to decimal in the bottom query

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[bborissov]

When you use UNION why not use proper types for empty columns which should be populated? That will increase the performance because SQL Server didn't need to implicitly convert your values to the defined types:

SELECT year(effmonth) as effyear,
       REGION,
       CARRIER,
       PCPNAME,
       HIC,
       AVGRISK,
       AVG(PREMPAY) AS AVGPREM,
       0.0 as PROJ08,
       ' ' AS txtMemberID_PHN,
       CASE
           WHEN AVGRISK BETWEEN 0   AND 0.5 THEN 'RISK 0-.5'
           WHEN AVGRISK BETWEEN 0.5 AND 1   THEN 'RISK 0.5-1'
           WHEN AVGRISK BETWEEN 1   AND 2   THEN 'RISK 1-2'
           WHEN AVGRISK BETWEEN 2   AND 3   THEN 'RISK 2-3'
           WHEN AVGRISK BETWEEN 3   AND 4   THEN 'RISK 3-4'
           ELSE 'RISK OVER 4'
       END AS RISKGRP
FROM #MEMBPREM
GROUP BY year(effmonth), REGION,
        CARRIER, PCPNAME, HIC, AVGRISK
UNION
SELECT  0  as effyear,
       ' ' as REGION,
       ' ' as CARRIER,
       ' ' as PCPNAME,
       ' ' as HIC,
       0   as AVGRISK,
       0   AS AVGPREM,
       PROJ08,
       txtMemberID_PHN,
       CASE
           WHEN PROJ08 BETWEEN 0   AND 0.5 THEN 'RISK 0-.5'
           WHEN PROJ08 BETWEEN 0.5 AND 1   THEN 'RISK 0.5-1'
           WHEN PROJ08 BETWEEN 1   AND 2   THEN 'RISK 1-2'
           WHEN PROJ08 BETWEEN 2   AND 3   THEN 'RISK 2-3'
           WHEN PROJ08 BETWEEN 3   AND 4   THEN 'RISK 3-4'
           WHEN PROJ08 IS NULL THEN 'NULL'
           ELSE 'RISK OVER 4'
       END AS RISKGRP
FROM #MEMBPREM
GROUP BY year(effmonth), REGION, CARRIER,
         PCPNAME, HIC, AVGRISK,PROJ08, txtMemberID_PHN

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP