recursive report

[alvintkl]

i need help

i want to do a BOM report(Build of Material)

i have two table call bom_mst and bom_detail

in bom_mst will have data like below

bommst_code

2110000000000150

2110230000025220

2110230000025210

 

in bom_detail have data below

bommst_code                         subbom_code

2110000000000150               2110230000025220

2110000000000150               2110230000028850

2110230000025220               2110230000025210

2110230000025220               2110230000025270

2110230000025210               2110010000012450

2110230000025210               2110010000012850

2110230000025210               2110010000013250

how to do the report

so it will print out like

2110000000000150
         2110230000025220
                 2110230000025210
                          2110010000012450
                          2110010000012850
                          2110010000013250

                 2110230000025270
         2110230000028850

[slow]

what you are talking about is a hierarchical query/report.

what database you use? each database uses sometimes custom method to obtain hierarchical results...

this is the example with oracle DB:

(suppose the second table is called tmp_sub, whit column name "cod | sub")
(I tested it on my local Oracle inatallation with this name :) )

you can use :
select lpad(' ',2*(level-1)) || sub  , level
  from tmp_sub
  start with cod = '2110000000000150'
  connect by prior sub = cod;

imagine to cycle the '2110000000000150'  selecting it from a main report and executing this query in subreport, using the "level" value to pad the obtained results.

you obtain this values:

2110230000025220        1
  2110230000025210      2
    2110010000012450    3
    2110010000012850    3
    2110230000025250    3
  2110230000025270      2
2110230000028850        1

another general solution (i prefer) is using the join clause:

SELECT ts1.cod, ts1.sub AS lev1, ts2.sub as lev2, ts3.sub as lev3, ts4.sub as lev4
FROM tmp_sub  ts1
    LEFT JOIN tmp_sub  ts2 ON ts2.cod = ts1.sub
    LEFT JOIN tmp_sub  ts3 ON ts3.cod = ts2.sub
    LEFT JOIN tmp_sub  ts4 ON ts4.cod = ts3.sub
WHERE ts1.cod not in (select distinct t.sub from tmp_sub t)
order by 1,2,3,4,5

in this case you obtain this results:

COD                 LEV1                LEV2                LEV3              LEV4
2110000000000150    2110230000025220    2110230000025210    2110010000012450   
2110000000000150    2110230000025220    2110230000025210    2110010000012850   
2110000000000150    2110230000025220    2110230000025210    2110230000025250   
2110000000000150    2110230000025220    2110230000025270       
2110000000000150    2110230000028850           

now you can use groups to print the obtained values, avoiding to print the repeated ones.
if you have more and more levels of parent/child relationship in your table, then you can add more "join" relationships in your query.

I tested and used it and it works well.

Give us your feedback...and:

_________________________________________

if you like it... give me KARMA points please!    : ) 
_________________________________________

listening:   Ravel - Bolero

 

 

[alvintkl]

i using postgres8.1 and using ireport3.7.1 to do the report

[alvintkl]

thank slow i use second method it work , but it got limit on the level have to add according to the data

this is my sql look like

SELECT ts1.bommst_code,st1.stock_name as stockname1,1.00 as qty_req1, ts1.subbom_code AS lev1,st2.stock_name as stockname2,bd1.qty_req as qty_req2,ts2.subbom_code as lev2,st3.stock_name as stockname3,bd2.qty_req as qty_req3,ts3.subbom_code as lev3,st4.stock_name as stockname4,bd3.qty_req as qty_req4,ts4.subbom_code as lev4,st5.stock_name as stockname5,bd4.qty_req as qty_req5

FROM bom_detail ts1

Left join stockitem st1 on ts1.bommst_code=st1.stock_nbr and ts1.co_id=st1.co_id

LEFT JOIN bom_detail ts2 ON ts2.bommst_code = ts1.subbom_code

Left join stockitem st2 on ts1.subbom_code=st2.stock_nbr and ts1.co_id=st2.co_id

Left join bom_detail bd1 on bd1.subbom_code=ts1.subbom_code and bd1.co_id=ts1.co_id

LEFT JOIN bom_detail ts3 ON ts3.bommst_code = ts2.subbom_code

Left join stockitem st3 on ts2.subbom_code=st3.stock_nbr and ts2.co_id=st3.co_id

Left join bom_detail bd2 on bd2.subbom_code=ts2.subbom_code and bd2.co_id=ts2.co_id

LEFT JOIN bom_detail ts4 ON ts4.bommst_code = ts3.subbom_code

Left join stockitem st4 on ts3.subbom_code=st4.stock_nbr and ts3.co_id=st4.co_id

Left join bom_detail bd3 on bd3.subbom_code=ts3.subbom_code and bd3.co_id=ts3.co_id

LEFT JOIN bom_detail ts5 ON ts5.bommst_code = ts4.subbom_code

Left join stockitem st5 on ts4.subbom_code=st5.stock_nbr and ts4.co_id=st5.co_id

Left join bom_detail bd4 on bd4.subbom_code=ts4.subbom_code and bd4.co_id=ts4.co_id

WHERE ts1.bommst_code not in (select distinct t.subbom_code from bom_detail t) and ts1.bommst_code=$P{stock_nbr}

order by 1,2,3,4,5

[slow]

I think there is no way to obtain a recursive and generic  query with simple sql code... so you must replicate your code to obtain a right level and show your data.

you can study postgres technique to obtain hierarchical query if you prefer... but , if you know the max level deep I think this is the best generic way to do it.

good report :)