1

I have a table with two columns, PARENT_PART & CHILD_PART.

I need all the linked details if the part is entered. All the child parts for that part need to be in the results.

Parent part Child part
OABCDEFG01  OABCDEFG02
OABCDEFG01  OABCDEFG03
OABCDEFG02  OABCDEFG04
OABCDEFG02  OABCDEFG05
OABCDEFG03  OABCDEFG06
OABCDEFG03  OABCDEFG07
OABCDEFG03  OABCDEFG08
OABCDEFG04  OABCDEFG09
OABCDEFG04  OABCDEFG10
OABCDEFG05  OABCDEFG11
OABCDEFG05  OABCDEFG12
OABCDEFG06  OABCDEFG13
OABCDEFG07  OABCDEFG14
OABCDEFG07  OABCDEFG15
OABCDEFG07  OABCDEFG16
OABCDEFG07  OABCDEFG17
OABCDEFG08  OABCDEFG18
OABCDEFG08  OABCDEFG19

As per above table if I have given PARENT-PART = 'OABCDEFG02'

RESULT SHOULD BE AS FOLLOWS: 

PARENT-PART        CHILD-PART 
OABCDEFG02         OABCDEFG04 
OABCDEFG02         OABCDEFG05 
OABCDEFG04         OABCDEFG09
OABCDEFG04         OABCDEFG10 
OABCDEFG05         OABCDEFG11
OABCDEFG05         OABCDEFG12

I am using DB2 for z/OS (Mainframe system).

Patrick Bossman
  • 139
  • 4
muralikrishna chepuri
  • 21
  • 3
  • Feel free to edit again. Please don't remove the formatting this time. Without the four blanks in front of your data it all gets mingled together. – Bill Woodger Sep 15 '15 at 20:13
  • It is unclear if you stop after using the first child as a parent, or if you would continue if the data was there. Can you clarify that with your sample data and your output. Can you also include the query that you are trying to use, as it will give some ideas about the parts you are finding difficult. – Bill Woodger Sep 15 '15 at 20:15

1 Answers1

3

You need a so-called "common table expression" for that. It is a recursive query that was added to the SQL standard and most database systems to produce the bill of material. I don't have a DB2 for z/OS availabe and cannot test, but here is the link to the documentation of common table expressions incl. some examples.

Your query will look like this:

WITH RPL (PART, CHILDPART) AS
     (  SELECT ROOT.PART, ROOT.CHILDPART
        FROM PARTLIST ROOT
        WHERE ROOT.PART = 'OABCDEFG02'
      UNION ALL
        SELECT CHILD.PART, CHILD.CHILDPART
        FROM RPL PARENT, PARTLIST CHILD
        WHERE  PARENT.CHILDPART = CHILD.PART
     )
SELECT DISTINCT PART, CHILDPART
 FROM RPL
  ORDER BY PART, CHILDPART
data_henrik
  • 16,724
  • 2
  • 28
  • 49