Re: SQL Statement help - recursive join and SUM statement

I think you have problems with joins. And it's kinda hard to figure them out close to midnight. Didn't you try to use connect by in inline view? This is the best way to build hierarchies

Mechanical Mann wrote:

> Sorry for the length of this post.
> for easy viewing try 60 col fixed font
>
> 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)
> t$kost NOT NULL NUMBER - movement type
> t$quan NOT NULL NUMBER - quantity
> t$trdt NOT NULL DATE - trans date
>
>
>
> A skeleton report for the ITEM-BOM path works fine:
>
> 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.%';
>
>
>
> A single SUM statement works fine...BUT
> the problem arises that when I incorporate a second
> SUM statement for the useage on intermediate items
>
> SELECT ai.t$item "R_Item", ai.t$dsca "R_Desc",
> 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;
>
>
> 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.
>
>
> ************* AND NOW MY QUESTIONS **************
>
>
> 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 Fri Jan 18 2002 - 06:45:34 CET