Union query giving incorrect resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Union query giving incorrect resultset

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-04-06 : 18:03:46

I’ve a question. I’m trying to execute a union query in Oracle10g (linked tables to Sql Server 2005). I was able to run the query but, in the resultset it is not showing the Total_Cost which is located in the second select query. The only difference in the 2 queries is the Total Price and Total_Cost rest of the columns are all same. Isn’t it true that only the datatype should match for a union query and not the column names? I tried UNION operator also yet, I get the same result.

Can anybody let me know what's wrong ?

Here is the query that I ran
*********************************************************************************

SELECT ORDERS.ORDER_ID,
ORDERS.ORDER_DTM,
ORDERS.ORDER_COMPLETION_TIME,
Sum(Decode(PRICE.ACTUAL_PRICE,0,'0',PRICE.ACTUAL_PRICE)) AS TOTAL_PRICE,
Decode(ORDERS.CANCELLED_IND, 1,'Yes',null) AS Cancelled,
CONTACT.COMPANY,
ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,
SERVICE.SERVICE_NM AS Service,
ORDERS.EXPORTED_DTM
FROM ORDERS,
ORDER_STATE,
ACCOUNT,
CONTACT,
SERVICE,
PRICE
WHERE PRICE.ORDER_ID = ORDERS.ORDER_ID
AND PRICE.DELETED_IND = 0
AND ORDERS.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
AND ACCOUNT.CONTACT_ID = CONTACT.CONTACT_ID
AND ORDERS.SERVICE_ID = SERVICE.SERVICE_ID
AND ORDERS.ORDER_DTM > trunc(sysdate) - 150
AND ORDERS.EXPORTED_DTM Is Null
AND ORDERS.ORDER_STATE_CD <>'OC'
AND ORDERS.ORDER_STATE_CD <>'CNC'
GROUP BY ORDERS.ORDER_ID,
ORDERS.ORDER_DTM,
ORDERS.ORDER_COMPLETION_TIME,
Decode(ORDERS.CANCELLED_IND, 1,'Yes',null),
ORDERS.EXPORTED_DTM,
CONTACT.COMPANY,
ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,
SERVICE.SERVICE_NM,
ORDERS.EXPORTED_DTM

UNION ALL

SELECT ORDERS.ORDER_ID,
ORDERS.ORDER_DTM,
ORDERS.ORDER_COMPLETION_TIME,
Sum(Decode(VENDOR_COST.ACTUAL_COST,0,'0',VENDOR_COST.ACTUAL_COST)) AS TOTAL_COST,
Decode(ORDERS.CANCELLED_IND, 1,'Yes',null) AS Cancelled,
CONTACT.COMPANY,
ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,
SERVICE.SERVICE_NM AS Service,
ORDERS.EXPORTED_DTM
FROM ORDERS,
ORDER_STATE,
ACCOUNT,
CONTACT,
SERVICE,
VENDOR_COST
WHERE VENDOR_COST.ORDER_ID = ORDERS.ORDER_ID
AND VENDOR_COST.DELETED_IND = 0
AND ORDERS.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
AND ACCOUNT.CONTACT_ID = CONTACT.CONTACT_ID
AND ORDERS.SERVICE_ID = SERVICE.SERVICE_ID
AND ORDERS.ORDER_DTM > trunc(sysdate) - 150
AND ORDERS.EXPORTED_DTM Is Null
AND ORDERS.ORDER_STATE_CD <>'OC'
AND ORDERS.ORDER_STATE_CD <>'CNC'
GROUP BY ORDERS.ORDER_ID,
ORDERS.ORDER_DTM,
ORDERS.ORDER_COMPLETION_TIME,
Decode(ORDERS.CANCELLED_IND, 1,'Yes',null),
ORDERS.EXPORTED_DTM,
CONTACT.COMPANY,
ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,
SERVICE.SERVICE_NM,
ORDERS.EXPORTED_DTM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 18:04:57

Since your question is not related to Reporting Services or Analysis Services, I am going to move the topic to a different forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources