SQL Statement help - recursive join and SUM statement
Date: Tue, 15 Jan 2002 16:15:26 -0600
Message-ID: <a229q7$6q1$1_at_bob.news.rcn.net>
Sorry for the length of this post.
I'm developing a where used report for a manufacturing
company. The report takes a "raw item" looks upstream
in the BOM 2 levels to show what the material is made
into and pulls useage qtys from an inventory table.
Table/Col info for the 3 tables
Item Master (ttiitm001100)
t$item NOT NULL CHAR(16) - item number
t$dsca NOT NULL CHAR(30) - description
Bill of Material (ttibom010100)
t$mitm NOT NULL CHAR(16) - manf. item (assy.)
t$sitm NOT NULL CHAR(16) - sub item (component)
Inventory movements (ttdinv700100)
A skeleton report for the ITEM-BOM path works fine:
A single SUM statement works fine...BUT
SELECT ai.t$item "R_Item", ai.t$dsca "R_Desc",
When the above statement runs it takes very long and
the SUM cols have inflated values. I think there's
some recursion going on here. And I think there's
some recursion going on here.
I tried a subselect as the WHERE clause for both
am.t$quan and bm.t$quan...no good.
for easy viewing try 60 col fixed font
t$kost NOT NULL NUMBER - movement type
t$quan NOT NULL NUMBER - quantity
t$trdt NOT NULL DATE - trans date
SELECT ai.t$item "R_Item", ai.t$dsca "R_Desc",
bi.t$item "M_Item", bi.t$dsca "M_Desc",
ci.t$item "F_Item", ci.t$dsca "F_Desc"
FROM baan.ttiitm001100 ai, baan.ttibom010100 ab,
baan.ttiitm001100 bi, baan.ttibom010100 bb,
baan.ttiitm001100 ci
WHERE ai.t$item = ab.t$sitm(+)
AND ab.t$mitm = bi.t$item(+)
AND bi.t$item = bb.t$sitm(+)
AND bb.t$mitm = ci.t$item(+)
AND ai.t$item LIKE '&item_num.%';
the problem arises that when I incorporate a second
SUM statement for the useage on intermediate items
SUM(am.t$quan) "R_Issue",
bi.t$item "M_Item", bi.t$dsca "M_Desc",
SUM(bm.t$quan) "M_Issue"
FROM baan.ttiitm001100 ai, baan.ttdinv700100 am,
baan.ttibom010100 ab,
baan.ttiitm001100 bi, baan.ttdinv700100 bm,
baan.ttibom010100 bb
WHERE ai.t$item LIKE '&item_num.%'
AND ai.t$item = am.t$item(+)
AND ai.t$item = ab.t$sitm(+)
AND ab.t$mitm = bi.t$item(+)
AND am.t$kost IN (6, 7)
AND am.t$trdt BETWEEN (sysdate - 365)
AND sysdate
AND bi.t$item = bm.t$item(+)
AND bi.t$item = bb.t$sitm(+)
AND bm.t$kost IN (6, 7)
AND bm.t$trdt BETWEEN (sysdate - 365)
AND sysdate
GROUP BY ai.t$item, ai.t$dsca, bi.t$item, bi.t$dsca;
Can I create a query that will recursively join
2 tables and SUM rows from a third table?
Can this be done w/o procedural logic?
Thanks in advance. Received on Tue Jan 15 2002 - 23:15:26 CET