2 Replies Latest reply on Feb 26, 2018 6:23 PM by NorbertKl

    Formating of merge : merge_insert_clause

    NorbertKl

      Hello all,

      i have some problem to format a merge statement as I want to . I tried to wrap my head around vadims blog posts about this topic but I fear they are above my pay grade. consider the following example.

      MERGE INTO crew tgt USING ( SELECT

           crew_seqno

          ,inoutdte

          ,cardtype

          ,tfirstnm

                                   FROM

           crew@xxx

                                   WHERE

           TO_CHAR(

               create_date

              ,'YYYYMMDD'

           ) = p_create_date_yyyymmdd

      )

      src ON ( src.crew_seqno = tgt.crew_seqno )

           WHEN NOT MATCHED THEN INSERT (

               crew_seqno                   -- this should be lined up

                     ,inoutdte              -- this should be lined up

                  ,cardtype                 -- this should be lined up

              ,tfirstnm                     -- this should be lined up

           ) VALUES (

               src.crew_seqno               -- this should be lined up

                      ,src.inoutdte         -- this should be lined up

                  ,src.cardtype             -- this should be lined up

              ,src.tfirstnm                 -- this should be lined up

           )

      WHEN MATCHED THEN UPDATE SET tgt.inoutdte = src.inoutdte

      ,tgt.cardtype = src.cardtype

      ,tgt.tfirstnm = src.tfirstnm;

       

      It should look like this

       

      MERGE INTO crew tgt USING ( SELECT

           crew_seqno

          ,inoutdte

          ,cardtype

          ,tfirstnm

                                   FROM

           crew@xxx

                                   WHERE

           TO_CHAR(

               create_date

              ,'YYYYMMDD'

           ) = p_create_date_yyyymmdd

      )

      src ON ( src.crew_seqno = tgt.crew_seqno )

           WHEN NOT MATCHED THEN INSERT (

               crew_seqno                  

              ,inoutdte             

              ,cardtype                

              ,tfirstnm                    

           ) VALUES (

               src.crew_seqno              

              ,src.inoutdte        

              ,src.cardtype            

              ,src.tfirstnm                

           )

      WHEN MATCHED THEN UPDATE SET tgt.inoutdte = src.inoutdte

      ,tgt.cardtype = src.cardtype

      ,tgt.tfirstnm = src.tfirstnm;

       

      How can I achieve this result ?

      I use version 17.4

       

      Thanks a lot for your help.

       

      Norbert