Re: SQL Statement help - recursive join and SUM statement

"Mechanical Mann" <eric_at_REMOVE_SPAMspeco.com> wrote in message news:<a229q7$6q1$1_at_bob.news.rcn.net>...
> 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.

hi
The question is bit confusing because, there is no relation between the Inventory movements table and the rest of the two tables.and also in the SUM statement given in the posting you are referencing a column in the inventory movments table which does not exsist. please notice below the places marked with "<<" and ">>"

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

from little bit that i am able to understand, i suggest the solution for this situation will be to use inline view i.e use a separate select query for summing the quantities in the from clause of the main select statement i.e..

SELECT ai.t$item "R_Item", ai.t$dsca, am.R_Desc, issue R_issue,bi.t$item "M_Item", bi.t$dsca "M_Desc", am.issue M_issue FROM (select ai.t$item, sum(am.t$quan) issue from baan.ttiitm001100 ai,baan.baan.ttdinv700100 am where ai.t$item LIKE '&item_num.%'AND am.t$kost IN (6, 7) AND am.t$trdt BETWEEN (sysdate - 365) AND sysdate group by ai.t$item) am,baan.ttibom010100 ab,

          baan.ttiitm001100 bi,  baan.ttibom010100 bb
      WHERE  ai.t$item LIKE '&item_num.%'
     AND  ai.t$item = ab.t$sitm(+)
     AND  ab.t$mitm = bi.t$item(+)
     AND  bi.t$item = bb.t$sitm(+);

If you can provide the following information i can give better solution.
1.What do you mean by a query that will recursively join   2 tables and SUM rows from a third table? please eloborate on that?

2. What is the relation between inventory movements table and the remaning table?

3. If possible provide the test data as actual data is not possible

regards
Raghu Ram
Certified DBA Received on Sat Jan 19 2002 - 08:30:09 CET