8 Replies Latest reply: Apr 22, 2013 5:44 PM by davidp 2 RSS

    Merge Vs Looping

    924460
      Hi,

      Following are my table structure.

      Table Name : CODE

      ID, Code, Level, Details [ Note : ID is Auto generated by sequence]


      I have a job that runs everyday to hit the remote view to pull the data into CODE table. View structure and Sample data in the remote view as follows

      Structure : Level0_Code, Level1_Code, Level2_Code , Program
      Sample Data : 'AA1234', 'XX1234','Zinc1543', MU

      I want to insert the the above row into CODE table as follows.

      1, AA1234, Level0,MU
      2, XX1234, Level1, MU
      3, Zinc1543, Level2, MU


      The remote view will have 50000 rows. Is it possible to do this using Merge clause b/w Remote view and my table? If yes could you please show me some sample.
      I definitely know this can be done with the help of Looping technique.
        • 1. Re: Merge Vs Looping
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Merge Vs Looping
            924460
            Hey Man,

            I hope i missed the DB version as Oracle10g. Apart from this do you have any concern for giving reply?
            • 3. Re: Merge Vs Looping
              924460
              Can any one please help me on this ?
              • 4. Re: Merge Vs Looping
                Etbin
                Take a look at unpivot keyword in http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF01702

                Regards

                Etbin
                • 5. Re: Merge Vs Looping
                  924460
                  Hi Edbin.

                  It's so high level and it needs much time to understand how it works. Could you please give some sample for my scenario
                  • 6. Re: Merge Vs Looping
                    Etbin
                    using http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDFIIDD as a pattern maybe
                    NOT TESTED!
                    select the_value,the_column,program 
                      from (select 'AA1234' level0_code,
                                   'XX1234' level1_code,
                                   'Zinc1543' level2_code,
                                   'MU' program
                              from dual
                           )
                    unpivot (the_value for the_column in (level0_code as 'level0_code',
                                                          level1_code as 'level1_code',
                                                          level2_code as 'level2_code'
                                                         )
                            )
                    Regards

                    Etbin
                    • 7. Re: Merge Vs Looping
                      924460
                      Thanks Edbin, Appreciate your prompt responses.
                      • 8. Re: Merge Vs Looping
                        davidp 2
                        A beautiful use of UNPIVOT Etbin, and a very nice solution. I need to remember and use PIVOT/UNPIVOT.

                        Unfortunately the original poster says the version is Oracle 10g, and UNPIVOT was introduced in Oracle 11.1

                        The (more clumsy) Oracle 10.2 version is
                        select seq_id.nextval, q.* from
                        (select CASE p.lvl when 1 then level0_code when 2 then level1_code when 3 then level2_code end as code
                             , CASE p.lvl when 1 then 'Level0' when 2 then 'Level1' when 3 then 'Level2' end as lev
                             , program
                          from (select 'AA1234' level0_code,
                                       'XX1234' level1_code,
                                       'Zinc1543' level2_code,
                                       'MU' program
                                  from dual
                               ) src
                           cross join (select level lvl from dual connect by level <= 3) p
                        order by level0_code, level1_code, level2_code, program
                        ) q
                        The (select level lvl from dual connect by level <= 3) generates numbers 1,2,3 to use to pick out the level0, level1 and level2 codes respectively.
                        If you want the ID's from a single row sequential, you will need an "order by" - otherwise the query could come out with all rows for level0 followed by all rows for level1 etc. If you don't mind the order then you can omit the outer select and the order by.

                        regards,
                        David