5 Replies Latest reply on Jul 20, 2014 7:20 PM by purushotham.infca@gmail.com

    Parent and Child records in Separate Rows

    New Member

      Hi,

       

      I have two tables INVOICE and LINE_ITEM which has Foregin key relatioship though the INVOICE_NO.

       

      INVOICE(INVOICE_NO(PK),REP_NO,CUST_NO)

       

      Sample Data :-

      INVOICE_NO             REP_NO                  CUST_NO

      8001                         1018                         121

      8002                         1013                         125

       

       

      LINE_ITEM(LINE_ITEM_NO,INVOICE_NO(FK),PRODUCT_CODE)

       

      Sample Data:

      LINE_ITEM_NO                    INVOICE_NO               PRODUCT_CODE

      189                                            8001                            112

      15                                              8002                            110    

      16                                              8002                             111

       

       

      I would like to generate the File  with Parent record in Line 1 followed by the Child records in next lines.If 8001 is first record, itshould have the LINE_ITEMS in next lines for 8001 INVOICE_NO.Similarly followed by another invoice 8002 and its LINE_ITEMS in next lines.

       

      Sample Output data in FF :-

       

      8001               1018                         121

      189                 8001                         112---------This line should contain the LINE_ITEMS for INVOICE_NO=8001 which is in above line--Child record

      8002               1013                         125

      15                    8002                        110  ---This line should contain the LINE_ITEMS for INVOICE_NO=8002 which is in above line

      16                    8002                        111  ---This line should contain the LINE_ITEMS for INVOICE_NO=8002 which is in above line---Child Record

       

      I have tried this with Constraint Based Loading by creating two target file definitions and appending the data into same files.

       

      ODS_INVOICE_FF,ODS_LINE_ITEM_FF.....Created foreign key relationship by INVOICE_NO.

       

       

      Any ideas will really appreciated.