Reputation: 591
I have below two queries which are giving the results
select order.ORDER_NUMBER,
upc.PROD_ID,
shipmentItem.upc_id,
order.ORDER_DATE
from shipment shipment,
Order order, shipment_item shipmentItem, UPC upc
where order.ORDER_DATE > current timestamp - 24
months
and order.order_number = shipment.order_number
and shipmentItem.SHIPMENT_ID = shipment.SHIPMENT_ID
and order.user_id = '123456'
and upc.UPC_ID = shipmentItem.UPC_ID
order by order.ORDER_DATE desc
which gives the below output
Order_number prod_id upc_id order_date
1234507 6489785 38890630 2018-05-27 13:12:47.564791
1234504 1310 10855 2018-05-27 13:10:41.475177
1234504 448832 23431431 2018-05-27 13:10:41.475177
And another query with conditional aggregation as
select upc_id,
max(case
when attr_name = 'COLOR_NORMAL' then
attr_value
end) as color,
max(case
when attr_name = 'SIZE' then
attr_value
end) as size
from Unary_Upc
where UPC_ID in (38890630, 10855, 23431431)
group by UPC_ID;
which gives the below output
upc_id color size
10855 (null) (null)
23431431 White (null)
38890630 Black 8
How to join the two above queries such that the output comes as below?
Order_number prod_id upc_id color size order_date
1234507 6489785 38890630 Black 8 2018-05-27 13:12:47.564791
1234504 1310 10855 (null) (null) 2018-05-27 13:10:41.475177
1234504 448832 23431431 White (null) 2018-05-27 13:10:41.475177
Upvotes: 1
Views: 92
Reputation: 65363
You can use two correlated subqueries with removed group by upc_id
against ORA-01422
exception, and write your query due to ANSI-92
format with aliases rather than full table names. By the way
order
is a reserved keyword, therefore it's not possible to create
a table named order
in Oracle, but the name "order"
is possible. replace current timestamp - 24 months
with current_timestamp - interval '24' month
select o.order_number, u.prod_id, si.upc_id,
(select max(case
when attr_name = 'COLOR_NORMAL' then
attr_value
end)
from Unary_Upc
where upc_id = u.upc_id
) as color,
(select max(case
when attr_name = 'SIZE' then
attr_value
end)
from Unary_Upc
where upc_id = u.upc_id
) as size,
o.order_date
from shipment s
join "order" o on o.order_number = s.order_number
join shipment_item si on si.shipment_id = s.shipment_id
join upc u on u.upc_id = si.upc_id
where order.order_date > current_timestamp - interval '24' month
and o.user_id = '123456'
order by o.order_date desc
Upvotes: 2
Reputation: 35920
You can achieve it by two methods:
---Method 1 ---
Join two subqueries as following:
SELECT
A.ORDER_NUMBER,
A.PROD_ID,
A.UPC_ID,
B.COLOR,
B.SIZE,
A.ORDER_DATE
FROM
(
SELECT
ORDER.ORDER_NUMBER,
UPC.PROD_ID,
SHIPMENTITEM.UPC_ID,
ORDER.ORDER_DATE
FROM
SHIPMENT SHIPMENT,
ORDER ORDER,
SHIPMENT_ITEM SHIPMENTITEM,
UPC UPC
WHERE
ORDER.ORDER_DATE > CURRENT timestamp - 24 months
AND ORDER.ORDER_NUMBER = SHIPMENT.ORDER_NUMBER
AND SHIPMENTITEM.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID
AND ORDER.USER_ID = '123456'
AND UPC.UPC_ID = SHIPMENTITEM.UPC_ID
) A
JOIN (
SELECT
UPC_ID,
MAX(CASE
WHEN ATTR_NAME = 'COLOR_NORMAL' THEN ATTR_VALUE
END) AS COLOR,
MAX(CASE
WHEN ATTR_NAME = 'SIZE' THEN ATTR_VALUE
END) AS SIZE
FROM
UNARY_UPC_ATTR_VAL
WHERE
UPC_ID IN (
38890630,
10855,
23431431
)
GROUP BY
UPC_ID
) B ON ( A.UPC_ID = B.UPC_ID )
ORDER BY
A.ORDER_DATE DESC WITH UR
---Method 2 ---
Use UNARY_UPC_ATTR_VAL
table in the first query and do GROUP BY
as following:
SELECT
ORDER.ORDER_NUMBER,
UPC.PROD_ID,
SHIPMENTITEM.UPC_ID,
MAX(CASE
WHEN ATTR_NAME = 'COLOR_NORMAL' THEN ATTR_VALUE
END) AS COLOR,
MAX(CASE
WHEN ATTR_NAME = 'SIZE' THEN ATTR_VALUE
END) AS SIZE,
ORDER.ORDER_DATE
FROM
SHIPMENT SHIPMENT,
ORDER ORDER,
SHIPMENT_ITEM SHIPMENTITEM,
UPC UPC,
UNARY_UPC_ATTR_VAL UUAV -- ADDED THIS TABLE HERE
WHERE
order.ORDER_DATE >current timestamp - 24 months
AND ORDER.ORDER_NUMBER = SHIPMENT.ORDER_NUMBER
AND SHIPMENTITEM.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID
AND ORDER.USER_ID = '123456'
AND UPC.UPC_ID = SHIPMENTITEM.UPC_ID
-- ADDED FOLLOWING CONDITION AND JOIN HERE
AND UUAV.UPC_ID IN (
38890630,
10855,
23431431
)
AND UUAV.UPC_ID = UPC.UPC_ID
-- ADDED FOLLOWING GROUP BY
GROUP BY
ORDER.ORDER_NUMBER,
UPC.PROD_ID,
SHIPMENTITEM.UPC_ID,
ORDER.ORDER_DATE
ORDER BY
ORDER.ORDER_DATE DESC WITH UR
Hope, This will help you.
Cheers!!
Upvotes: 0