April 6, 2009 at 3:58 pm
I’ve a question. I’m trying to execute a union query in Oracle10g. 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.
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
April 7, 2009 at 1:26 pm
Your result set will show the fourth column as "TOTAL_PRICE" because this is the label you used in the first query. Data from your second query will be there no matter the column name is not the one you specified.
You cannot have two labels for a single column 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply