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



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




      Sample Data :-

      INVOICE_NO             REP_NO                  CUST_NO

      8001                         1018                         121

      8002                         1013                         125





      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.