Pankaj Arora Pankaj Arora - 5 months ago 12
SQL Question

Need Help in Achieving the required Output

I have a requirement to convert Data from certain tables into a specific format.
Could you please help me out achieve the desired output?

Below link contains an Excel Attachment which holds the following information:
Google Drive Link


  1. Tab : Input-Output --> Contains the Structure of the Source and the Target Table

  2. Tab : Create Scripts --> Create scripts to create all the tables

  3. Tab : Insert Scripts --> Insert script to insert the data into the Source Tables



Edit (Code) which I tried:

TYPE subline_col_c_rectype IS RECORD
(
ID_C VARCHAR(75),
Col_C1 VARCHAR(75),
Col_C2 VARCHAR(75)

);

TYPE subline_col_c_tabtype IS TABLE OF subline_col_c_rectype
INDEX BY BINARY_INTEGER;


TYPE line_rectype IS RECORD
(

ID_A VARCHAR(75),
Col_A1 VARCHAR(75),
Col_A2 VARCHAR(75),
Col_A3 VARCHAR(75),
Col_A4 VARCHAR(75),
Col_A5 VARCHAR(75),
ID_B VARCHAR(75),
Col_B1 VARCHAR(75),
Col_B2 VARCHAR(75),
Col_B3 VARCHAR(75),
Col_B4 VARCHAR(75),
Col_B5 VARCHAR(75),
Col_C subline_col_c_tabtype,
Col_D subline_col_c_tabtype
);

TYPE line_tabtype IS TABLE OF line_rectype
INDEX BY BINARY_INTEGER;


Was able to store the records in associative array line_tabtype but not sure how to expand the child records and store it in the target table.

Answer
SELECT ID_A, COL_A1, COL_A2, COL_A3, COL_A4, COL_A5, ID_B, COL_B1, COL_B2, COL_B3, COL_B4, COL_B5, ID_C, COL_C1, COL_C2, ID_D, COL_D1, COL_D2
  FROM (SELECT a.*, ROW_NUMBER () OVER (PARTITION BY id_a ORDER BY col_a1) rn_a
          FROM POC_SOURCE_A a) a
       FULL OUTER JOIN (SELECT NVL (x.id_x, d.id_d) id_y, NVL (x.rn_x, d.rn_d) rn_y, x.*, d.*
                          FROM (SELECT NVL (b.id_b, c.id_c) id_x, NVL (b.rn_b, c.rn_c) rn_x, b.*, c.*
                                  FROM (SELECT b.*, ROW_NUMBER () OVER (PARTITION BY id_b ORDER BY col_b1) rn_b
                                          FROM POC_SOURCE_B b) b
                                       FULL OUTER JOIN (SELECT c.*, ROW_NUMBER () OVER (PARTITION BY id_c ORDER BY col_c1) rn_c
                                                          FROM POC_SOURCE_C c) c
                                          ON b.id_b = c.id_c AND b.rn_b = c.rn_c) x
                               FULL OUTER JOIN (SELECT d.*, ROW_NUMBER () OVER (PARTITION BY id_d ORDER BY col_d1) rn_d
                                                  FROM POC_SOURCE_D d) d
                                  ON x.id_x = d.id_d AND x.rn_x = d.rn_d) y
          ON y.id_y = a.id_a AND y.rn_y = a.rn_a
ORDER BY NVL (ID_A, ID_Y), NVL (rn_a, rn_y)

EDIT:

DECLARE
   TYPE line_tabtype IS TABLE OF POC_TARGET_C%ROWTYPE
      INDEX BY BINARY_INTEGER;
   target line_tabtype;  
BEGIN
   FOR c1 IN (SELECT DISTINCT id_a id
                FROM POC_SOURCE_A
              UNION
              SELECT DISTINCT id_b id
                FROM POC_SOURCE_B
              UNION
              SELECT DISTINCT id_c id
                FROM POC_SOURCE_C
              UNION
              SELECT DISTINCT id_d id
                FROM POC_SOURCE_D
              ORDER BY 1)
   LOOP
      FOR c2 IN (  SELECT a.*, ROW_NUMBER () OVER (ORDER BY col_a1) rn
                     FROM POC_SOURCE_A a
                    WHERE ID_A = c1.ID
                 ORDER BY COL_A1)
      LOOP
         target (c2.rn).ID_A := c2.ID_A;
         target (c2.rn).COL_A1 := c2.COL_A1;
         target (c2.rn).COL_A2 := c2.COL_A2;
         target (c2.rn).COL_A3 := c2.COL_A3;
         target (c2.rn).COL_A4 := c2.COL_A4;
         target (c2.rn).COL_A5 := c2.COL_A5;
      END LOOP;

      FOR c2 IN (  SELECT b.*, ROW_NUMBER () OVER (ORDER BY col_b1) rn
                     FROM POC_SOURCE_B b
                    WHERE ID_B = c1.ID
                 ORDER BY COL_B1)
      LOOP
         target (c2.rn).ID_B := c2.ID_B;
         target (c2.rn).COL_B1 := c2.COL_B1;
         target (c2.rn).COL_B2 := c2.COL_B2;
         target (c2.rn).COL_B3 := c2.COL_B3;
         target (c2.rn).COL_B4 := c2.COL_B4;
         target (c2.rn).COL_B5 := c2.COL_B5;
      END LOOP;

      FOR c2 IN (  SELECT c.*, ROW_NUMBER () OVER (ORDER BY col_c1) rn
                     FROM POC_SOURCE_C c
                    WHERE ID_C = c1.ID
                 ORDER BY COL_C1)
      LOOP
         target (c2.rn).ID_C := c2.ID_C;
         target (c2.rn).COL_C1 := c2.COL_C1;
         target (c2.rn).COL_C2 := c2.COL_C2;
      END LOOP;

      FOR c2 IN (  SELECT d.*, ROW_NUMBER () OVER (ORDER BY col_d1) rn
                     FROM POC_SOURCE_D D
                    WHERE ID_D = c1.ID
                 ORDER BY COL_D1)
      LOOP
         target (c2.rn).ID_D := c2.ID_D;
         target (c2.rn).COL_D1 := c2.COL_D1;
         target (c2.rn).COL_D2 := c2.COL_D2;
      END LOOP;

      FORALL I IN target.FIRST .. target.LAST
        INSERT INTO POC_TARGET_C VALUES target(i);

      target.delete();
   END LOOP;
END;
Comments