|
View previous topic :: View next topic
|
| Author |
Message |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Hi all,
Could anyone please give a query for Bill of Materials.
As you have already known,
Bill of materials (BOM) is the term used to describe the "parts list" of components needed to complete a saleable end-item.
BOMs are hierarchical in nature with the top level representing the sub-assembly or end-item. For example the end-item BOM for a Personal computer would list the computer, its major sub-assemblies (board, chassis, modem, keyboard, display, etc.) as well as additional materials needed for a complete saleable product -- shipping box, user manual, packaging, packaging labels, etc.
For my case, DB2 Table loks like below.
Major Part Minor part
P1 P2
P1 P5
P2 P3
P3 P6
P3 P7
P5 P2
... ...
... ...
Both columns together form a composite Primary Key.
Here, I want to find out What are the Minor Parts needed for building a Major part?
If you take P1 as Major part, it needs P2 and P5 as minor parts. And P2 needs P3. And P3 needs P6 and P7. And so on.
P2, P5, P3, P6, P7 are the Minor parts of Major part P1.
How to form a query to this requirement?
Here Major part will be given as input and Minor parts are expected as a result.
Please help me.
Thanks,
Gnanas |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
is this an homework or a real BOM issue ( serious question )
the approach might be different...
Usually when designiging a BOM application there are some business rules
which are defined according to the product being described..
( one of them - to keep the application SIMPLE - is to fix at the design stage the number of levels of Your BOM )
in this case You can do it with a single query joining the table for the number of levels defined beforehand...
if the number of levels does not have a limit, You must explode Your PN
programmatically with a recursive function... |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Thanks Enrico-sorichetti
It's a real time requirement.
I am not sure how many levels are enough. And it is needed to check.
But I believe all the levels should be searched by the query.
Say using COBOL or PLI, Programmatically how to make recursive call to find out?
Thanks,
Gnanas |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
It's a real time requirement.
I am not sure how many levels are enough. And it is needed to check.
But I believe all the levels should be searched by the query.
|
What does Your application design team say ???
why don' t You have a little speech with them and
post again the appropriate
BOM' s have many faces..
BOM' s processing are related also to the plant workflow... and organization
BOM' s have different different levels sophistication according to who is the person looking at the report...
is it a static or a parametric BOM application
that' why looking at Your O/P I was doubting...
the question is too simple for a real BOM application |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Thanks Enrico-sorichetti.
I have asked this to Application Design team. I am waiting for the reply.
I will come with the clear details soon.
Regards,
Gnanas. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what version db2 do you have? recursive sql is available using CTE's (common table expressions) in V8. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| We are using DB2 V8 only. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| We are using DB2 V8 only. |
I Feel that The db2 version is irrelevant in this case...
the design must choose the logic flow,
the DB2 facilities might help to write less code...
Nobody with a reasonable understanding of BOM processing
would base the application development on forum replies,
that' s why I asked if it was homework or not...
Usually ( at least were I was involved in building the teams ) the DB design was done with the help and APPROUVAL of DB support,
and all the relevant queries were modeled and tested by db support before being deployed to the programmers
in this case ( forget about the right syntax, it' s just the concept )
select lev0.pn, lev1.pn, lev2.pn,.... for the number of hardcoded levels from (pn_mastr lev0 ( pn_mast lev1
( I HAD A MODEL TESTED AND WORKING A LOONG TIME AGO, BUT THREW AWAY EVERITHING :-( )
will give the bom exploded view for n levels ...
but this will be only a theoretical query.. even if correct one from a sql point of view
and I would never dream to approach a BOM application in this way
BOM has too many nuances
for the relevant queries to solved by db2 advanced query techniques only |
|
| Back to top |
|
|
|
|
