5 Replies Latest reply on Feb 27, 2019 6:21 PM by Vadim Tropashko-Oracle

    Formatting - Merge statement indentation / line breaks

    jgebal

      Hello,

       

      I'm using SQLDeveloper 18.4

       

      With following format options selected:

       

      What I find really odd is the inconsistency of indentation in MERGE statements compared to INSERT/UPDATE statements.

      Some parts of MERGE can go quite far to the right making it hard to read the code, specially when MERGE is already indented inside PLSQL.

       

      Here is how it is today:

      insert into a_table (
        a,
        b,
        c
      ) values (
        1,
        'SOME VAL',
        3
      );
      
      
      merge into some_really_really_long_table_name using (
                                                           select
                                                             *
                                                             from
                                                             another_table
                                                         )
      on ( source_column1 = target_column1
         and source_column2 = target_column2 )
      when not matched then insert (
        target_column3,
        target_column3,
        some_long_column_name ) values (
        source_column3,
        source_column4,
        source_column5 )
      when matched then update set some_long_column_name = source_column3,
                                   target_column4 = source_column4,
                                   target_column5 = source_column5;
      
      
      merge into a_table using (
                                select
                                  *
                                  from
                                  another_table
                              )
      on ( source_column1 = target_column1
         and source_column2 = target_column2 )
      when not matched then insert (
        target_column3,
        target_column3,
        target_column5 ) values (
        source_column3,
        source_column4,
        source_column5 )
      when matched then update set target_column3 = source_column3,
                                   target_column4 = source_column4,
                                   target_column5 = source_column5;
      
      
      update a_table
         set
        some_column = a_value,
        some_column = a_value,
        some_column = a_value,
        some_column = a_value
       where
        another_column = a_another_value;
      

       

       

      What I would like to see however is more like this:

      insert into a_table (
        a,
        b,
        c ) 
      values (
        1,
        'SOME VAL',
        3 );
      
      
      merge into some_really_really_long_table_name
       using (
               select
                 *
                 from
                 another_table
             )
      on ( source_column1 = target_column1
         and source_column2 = target_column2 )
      when not matched then
        insert (
          target_column3,
          target_column3,
          some_long_column_name ) 
        values (
          source_column3,
          source_column4,
          source_column5 )
      when matched then update 
        set some_long_column_name = source_column3,
            target_column4 = source_column4,
            target_column5 = source_column5;
      
      
      merge into a_table
      using (
              select
                *
                from
                another_table
            )
      on ( source_column1 = target_column1
         and source_column2 = target_column2 )
      when not matched then 
        insert (
          target_column3,
          target_column3,
          target_column5 ) 
        values (
          source_column3,
          source_column4,
          source_column5 )
      when matched then update 
        set target_column3 = source_column3,
            target_column4 = source_column4,
            target_column5 = source_column5;
      
      
      update a_table
         set some_column = a_value,
             some_column = a_value,
             some_column = a_value,
             some_column = a_value
       where
        another_column = a_another_value;
      

       

      The ask.

      - Add line break before following keywords of merge statement and indent them using indentation depth:

      using, insert, update, values, set

      - keep columns indented after (or under) the set keyword

       

      Rationale:

      Current solution is making code hard to read as:

      - select in the USING clause is indented differently depending on the table name of the MERGE

      - column lists do not fall under the preceding keyword (values/insert/update)

      - column lists in update of merge is formatted&indented differently than in standalone update

      - list of columns of merge-update goes way too far to the right

      - select statement of merge-using goes way too far to the right

       

      The format definition language seems very powerful however I find it too hard to modify myself.

        • 1. Re: Formatting - Merge statement indentation / line breaks
          B.Delmée

          > The format definition language seems very powerful however I find it too hard to modify myself.

           

          this++

          arbori--

          • 2. Re: Formatting - Merge statement indentation / line breaks
            Vadim Tropashko-Oracle

            _extraBrkBefore:

              [node) 'USING'  & [node^) merge |                       -- added

              [node) 'INSERT' & [node^) merge_insert_clause |         -- added

              [node) 'VALUES' & [node^) merge_insert_clause |         -- added

              [node) 'SET'    & [node^) merge_update_clause |         -- added

              [node) physical_properties

            | [node) sql_statement & ![node^) identifier  -- if there is a parent of sql_statement with any payload (identifiers are always leaf nodes)

            ...

             

            Output with default advanced settings:

            MERGE INTO some_really_really_long_table_name

            USING (

                      SELECT

                          *

                      FROM

                          another_table

                  )

            ON ( source_column1 = target_column1

                 AND source_column2 = target_column2 )

            WHEN NOT MATCHED THEN

            INSERT (

                target_column3,

                target_column3,

                some_long_column_name )

            VALUES (

                source_column3,

                source_column4,

                source_column5 )

            WHEN MATCHED THEN UPDATE

            SET some_long_column_name = source_column3,

                target_column4 = source_column4,

                target_column5 = source_column5;

             

            I have checked in this amendment for 19.1

            1 person found this helpful
            • 4. Re: Formatting - Merge statement indentation / line breaks
              jgebal

              For some reason it doesn't always format correctly update part of merge.

              It depends on number of items in the set clause.

              Some parts are not properly formatted when set list is longer/shorter.

               

              Examples:

               

              merge into some_really_really_long_table_name

              using (

                      select

                        *

                        from

                        another_table

                    )

              on ( source_column1 = target_column1

                 and source_column2 = target_column2 )

              when not matched then

              insert (

                target_column3,

                target_column3,

                some_long_column_name )

              values (

                source_column3,

                source_column4,

                source_column5 )

              when matched then update

                 set some_long_column_name = source_column3,

              target_column4 = source_column4,

              target_column4 = source_column4,

                     target_column5 = source_column5;

               

              merge into some_really_really_long_table_name

              using (

                      select

                        *

                        from

                        another_table

                    )

              on ( source_column1 = target_column1

                 and source_column2 = target_column2 )

              when not matched then

              insert (

                target_column3,

                target_column3,

                some_long_column_name )

              values (

                source_column3,

                source_column4,

                source_column5 )

              when matched then update

                 set some_long_column_name = source_column3,

              target_column5 = source_column5;

              • 5. Re: Formatting - Merge statement indentation / line breaks
                Vadim Tropashko-Oracle

                pairwiseAlignments7_3:

                  [predecessor) column

                & ![predecessor-1) '='

                & [node) column

                & [node-1) ','

                & predecessor=ancestor-3

                & ancestor < node

                ;

                 

                pairwiseAlignments:

                    pairwiseAlignments1

                  | pairwiseAlignments2

                  | pairwiseAlignments3

                  | pairwiseAlignments4

                  | pairwiseAlignments5

                  | pairwiseAlignments6

                  | pairwiseAlignments7

                  | pairwiseAlignments7_3

                  | pairwiseAlignments8

                ->

                ;

                 

                Output:

                 

                WHEN MATCHED THEN UPDATE

                SET some_long_column_name = source_column3,

                    target_column4 = source_column4,

                    target_column4 = source_column4,

                    target_column5 = source_column5;

                 

                The reason for this cumbersome rule is the structure of syntax tree:

                 

                ...

                        [42,43)   'THEN'

                        [43,44)   'UPDATE'

                        [44,45)   'SET'

                        [45,46)   column  identifier

                        [46,47)   '='

                        [47,48)   column  ...

                        [48,60)   merge_update_clause[36,56)

                          [48,56)   merge_update_clause[36,56)

                            [48,52)   merge_update_clause[36,56)  merge_update_clause[36,56)#

                              [48,49)   ','

                              [49,50)   column  identifier

                              [50,51)   '='

                              [51,52)   column ...

                            [52,56)   merge_update_clause[36,56)#

                              [52,53)   ','

                              [53,54)   column  identifier

                              [54,55)   '='

                              [55,56)   column  ...

                ...

                 

                We want to align the node [49,50) with node [45,46) which is predecessor of predecessor of predecessor of the ancestor of [49,50)