SQL Union Query

[Dan]

Hi there,

I am trying to run a union query to return Net Sales as two different fields
for each year
eg

SELECT SALESREP AS NAME,
NET_SALES AS AMOUNT_2005,
'0.00' AS AMOUNT_2006
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN '01.01.05' AND '31.01.05'

UNION ALL

SELECT SALESREP AS NAME,
'0.00' AS AMOUNT_2005,
NET_SALES AS AMOUNT_2006
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN '01.01.06' AND '31.01.06'

I am receiving the error message that my data types are not compatible in a
fullselect.

Please would you let me know how to make these two datatypes correspond in
order to run thequery?

 

[Alex Dybenko]

Hi,
i think field NET_SALES has numeric type, and you enter fixed value for it
as '0.00' - this can be a problem

try to change '0.00' to 0 in both places

 

[Dan]

Thanks very much Alex, that worked fine

Hi,
i think field NET_SALES has numeric type, and you enter fixed value for it
as '0.00' - this can be a problem

try to change '0.00' to 0 in both places