Optimising SQL statement to use query but need to reference column outside subquery somehow
Is there any way in Oracle within an SQL query to reference a column from within a subquery that is outside of it, or to achieve that effect even if via a different means? Everything I've read on the web so far has just not helped with this.
For example (this is the kind of thing I'm after):
SELECT a.product_code, b.received, b.issued
FROM productinfo a,
(SELECT SUM(qty_received) AS received, SUM(qty_issued) AS issued
FROM productdetail b WHERE b.product_code = a.product_code AND active = 1);
I've tried loads of different variations/combinationsAt the moment I just get errors like ORA-00904: invalid identifier relating to the WHERE clause relationship.
At present if I run as seperate queries, e.g.:
SELECT product_code FROM productinfo;
and then for each of those records:
SELECT SUM(qty_received) AS received, SUM(qty_issued) AS issued FROM productdetail
WHERE product_code = '(specified)' AND active = 1;
This can take over an half an hour to run for 8000 records which is just plain daft.
Running out of hair, any help appreciate开发者_如何学运维d!! Thank you.
The ORA error is because you can't correlate the derived table/inline view - you need to use JOIN notation (ANSI-89 or 92).
Assuming you really need column(s) from the PRODUCTINFO
table, use:
SELECT a.product_code, b.received, b.issued
FROM PRODUCTINFO a
JOIN (SELECT t.product_code,
SUM(t.qty_received) AS received,
SUM(t.qty_issued) AS issued
FROM PRODUCTDETAIL t
WHERE t.active = 1
GROUP BY t.produce_code) b ON b.product_code = a.product_code
If you want to see a list of the productinfo records, which may or may not have PRODUCTDETAIL records, use:
SELECT a.product_code, b.received, b.issued
FROM PRODUCTINFO a
LEFT JOIN (SELECT t.product_code,
SUM(t.qty_received) AS received,
SUM(t.qty_issued) AS issued
FROM PRODUCTDETAIL t
WHERE t.active = 1
GROUP BY t.produce_code) b ON b.product_code = a.product_code
But the example looks like you might only need to use:
SELECT t.product_code,
SUM(t.qty_received) AS received,
SUM(t.qty_issued) AS issued
FROM PRODUCTDETAIL t
WHERE t.active = 1
GROUP BY t.produce_code
Just group by inside the query and use an inner join
SELECT a.product_code, b.received, b.issued
FROM productinfo a
INNER JOIN
(SELECT product_code, SUM(qty_received) AS received, SUM(qty_issued) AS issued
FROM productdetail
WHERE active = 1
GROUP BY product_code) b on b.product_code = a.product_code
精彩评论