SQL Union Error
I'm trying to create a report that shows both the actual budget numbers for each month.
Since I can't link them directly, i'm trying to use a union. Problem is that I'm getting the following error: ERROR converting data type varchar to numberic. Suggestions on how to get the following code to work?
Since I can't link them directly, i'm trying to use a union. Problem is that I'm getting the following error: ERROR converting data type varchar to numberic. Suggestions on how to get the following code to work?
select gla.COMPANY,
gla.FISCAL_YEAR,
-- '' AS BUDFY,
gla.ACCT_UNIT,
gla.ACCOUNT,
gla.SUB_ACCOUNT,
gla.DB_AMOUNT_01 + gla.CR_AMOUNT_01 GLAMONTH1,
gla.DB_AMOUNT_02 + gla.CR_AMOUNT_02 GLAMOUNT2,
gla.DB_AMOUNT_03 + gla.CR_AMOUNT_03 GLAMONTH3,
gla.DB_AMOUNT_04 + gla.CR_AMOUNT_04 GLAMONTH4,
gla.DB_AMOUNT_05 + gla.CR_AMOUNT_05 GLAMONTH5,
gla.DB_AMOUNT_06 + gla.CR_AMOUNT_06 GLAMONTH6,
gla.DB_AMOUNT_07 + gla.CR_AMOUNT_07 GLAMONTH7,
gla.DB_AMOUNT_08 + gla.CR_AMOUNT_08 GLAMONTH8,
gla.DB_AMOUNT_09 + gla.CR_AMOUNT_09 GLAMONTH9,
gla.DB_AMOUNT_10 + gla.CR_AMOUNT_10 GLAMONTH10,
gla.DB_AMOUNT_11 + gla.CR_AMOUNT_11 GLAMONTH11,
gla.DB_AMOUNT_12 + gla.CR_AMOUNT_12 GLAMONTH12,
'' BUDMONTH1,
'' BUDMONTH2,
'' BUDMONTH3,
'' BUDMONTH4,
'' BUDMONTH5,
'' BUDMONTH6,
'' BUDMONTH7,
'' BUDMONTH8,
'' BUDMONTH9,
'' BUDMONTH10,
'' BUDMONTH11,
'' BUDMONTH12
from GLAMOUNTS gla
where gla.ACCT_UNIT = 01000
and gla.ACCOUNT in (6050,6060)
union
select fbd.COMPANY,
-- '' AS GLFY,
fbd.FISCAL_YEAR,
fbd.ACCT_UNIT,
fbd.ACCOUNT,
fbd.SUB_ACCOUNT,
'' GLAMONTH1,
'' GLAMONTH2,
'' GLAMONTH3,
'' GLAMONTH4,
'' GLAMONTH5,
'' GLAMONTH6,
'' GLAMONTH7,
'' GLAMONTH8,
'' GLAMONTH9,
'' GLAMONTH10,
'' GLAMONTH11,
'' GLAMONTH12,
fbd.DB_AMOUNT_01 + fbd.CR_AMOUNT_01 BUDMONTH1,
fbd.DB_AMOUNT_02 + fbd.CR_AMOUNT_02 BUDMOUNT2,
fbd.DB_AMOUNT_03 + fbd.CR_AMOUNT_03 BUDMONTH3,
fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04 BUDMONTH4,
fbd.DB_AMOUNT_05 + fbd.CR_AMOUNT_05 BUDMONTH5,
fbd.DB_AMOUNT_06 + fbd.CR_AMOUNT_06 BUDMONTH6,
fbd.DB_AMOUNT_07 + fbd.CR_AMOUNT_07 BUDMONTH7,
fbd.DB_AMOUNT_08 + fbd.CR_AMOUNT_08 BUDMONTH8,
fbd.DB_AMOUNT_09 + fbd.CR_AMOUNT_09 BUDMONTH9,
fbd.DB_AMOUNT_10 + fbd.CR_AMOUNT_10 BUDMONTH10,
fbd.DB_AMOUNT_11 + fbd.CR_AMOUNT_11 BUDMONTH11,
fbd.DB_AMOUNT_12 + fbd.CR_AMOUNT_12 BUDMONTH12
from FBDETAIL fbd
where fbd.ACCT_UNIT = 01000
and fbd.ACCOUNT in (6050,6060)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this query, changed ordering of second SQL query from
fbd.COMPANY,
-- '' AS GLFY,
fbd.FISCAL_YEAR,
to
fbd.COMPANY,
fbd.FISCAL_YEAR,
-- '' AS GLFY,
hence the UNION fails.
fbd.COMPANY,
-- '' AS GLFY,
fbd.FISCAL_YEAR,
to
fbd.COMPANY,
fbd.FISCAL_YEAR,
-- '' AS GLFY,
hence the UNION fails.
select gla.COMPANY,
gla.FISCAL_YEAR,
-- '' AS BUDFY,
gla.ACCT_UNIT,
gla.ACCOUNT,
gla.SUB_ACCOUNT,
gla.DB_AMOUNT_01 + gla.CR_AMOUNT_01 GLAMONTH1,
gla.DB_AMOUNT_02 + gla.CR_AMOUNT_02 GLAMOUNT2,
gla.DB_AMOUNT_03 + gla.CR_AMOUNT_03 GLAMONTH3,
gla.DB_AMOUNT_04 + gla.CR_AMOUNT_04 GLAMONTH4,
gla.DB_AMOUNT_05 + gla.CR_AMOUNT_05 GLAMONTH5,
gla.DB_AMOUNT_06 + gla.CR_AMOUNT_06 GLAMONTH6,
gla.DB_AMOUNT_07 + gla.CR_AMOUNT_07 GLAMONTH7,
gla.DB_AMOUNT_08 + gla.CR_AMOUNT_08 GLAMONTH8,
gla.DB_AMOUNT_09 + gla.CR_AMOUNT_09 GLAMONTH9,
gla.DB_AMOUNT_10 + gla.CR_AMOUNT_10 GLAMONTH10,
gla.DB_AMOUNT_11 + gla.CR_AMOUNT_11 GLAMONTH11,
gla.DB_AMOUNT_12 + gla.CR_AMOUNT_12 GLAMONTH12,
'' BUDMONTH1,
'' BUDMONTH2,
'' BUDMONTH3,
'' BUDMONTH4,
'' BUDMONTH5,
'' BUDMONTH6,
'' BUDMONTH7,
'' BUDMONTH8,
'' BUDMONTH9,
'' BUDMONTH10,
'' BUDMONTH11,
'' BUDMONTH12
from GLAMOUNTS gla
where gla.ACCT_UNIT = 01000
and gla.ACCOUNT in (6050,6060)
union
select fbd.COMPANY,
fbd.FISCAL_YEAR,
-- '' AS GLFY,
fbd.ACCT_UNIT,
fbd.ACCOUNT,
fbd.SUB_ACCOUNT,
'' GLAMONTH1,
'' GLAMONTH2,
'' GLAMONTH3,
'' GLAMONTH4,
'' GLAMONTH5,
'' GLAMONTH6,
'' GLAMONTH7,
'' GLAMONTH8,
'' GLAMONTH9,
'' GLAMONTH10,
'' GLAMONTH11,
'' GLAMONTH12,
fbd.DB_AMOUNT_01 + fbd.CR_AMOUNT_01 BUDMONTH1,
fbd.DB_AMOUNT_02 + fbd.CR_AMOUNT_02 BUDMOUNT2,
fbd.DB_AMOUNT_03 + fbd.CR_AMOUNT_03 BUDMONTH3,
fbd.DB_AMOUNT_04 + fbd.CR_AMOUNT_04 BUDMONTH4,
fbd.DB_AMOUNT_05 + fbd.CR_AMOUNT_05 BUDMONTH5,
fbd.DB_AMOUNT_06 + fbd.CR_AMOUNT_06 BUDMONTH6,
fbd.DB_AMOUNT_07 + fbd.CR_AMOUNT_07 BUDMONTH7,
fbd.DB_AMOUNT_08 + fbd.CR_AMOUNT_08 BUDMONTH8,
fbd.DB_AMOUNT_09 + fbd.CR_AMOUNT_09 BUDMONTH9,
fbd.DB_AMOUNT_10 + fbd.CR_AMOUNT_10 BUDMONTH10,
fbd.DB_AMOUNT_11 + fbd.CR_AMOUNT_11 BUDMONTH11,
fbd.DB_AMOUNT_12 + fbd.CR_AMOUNT_12 BUDMONTH12
from FBDETAIL fbd
where fbd.ACCT_UNIT = 01000
and fbd.ACCOUNT in (6050,6060) SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One other item...
In your WHERE clause, you have "ACCT_UNIT = 01000". As most numbers do not start with a zero, should the value be enclosed in apostrophes, like this:
ACCT_UNIT = '01000'
In your WHERE clause, you have "ACCT_UNIT = 01000". As most numbers do not start with a zero, should the value be enclosed in apostrophes, like this:
ACCT_UNIT = '01000'
SELECT 'banana'
UNION
SELECT 42
will result in the same kind of error.
You'll have to go through both sides of the UNION and make sure each column both has the same data type: number above the UNION matches number below the union, dates to dates, bits to bits, etc.