Forum Stats

  • 3,784,143 Users
  • 2,254,897 Discussions
  • 7,880,709 Comments

Discussions

Formatting - Merge statement indentation / line breaks

jgebal
jgebal Member Posts: 21 Blue Ribbon
edited Mar 22, 2019 8:32AM in SQL Developer

Hello,

I'm using SQLDeveloper 18.4

pastedImage_0.png

With following format options selected:

pastedImage_1.png

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_tableusing (        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.

jgebalMarkusHohloch

Best Answer

  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,249 Employee
    edited Feb 27, 2019 1:21PM Accepted Answer

    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)

Answers

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 22, 2019 9:28AM

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

    this++

    arbori--

    MarkusHohloch
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,249 Employee
    edited Feb 22, 2019 1:21PM

    _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

    jgebaljgebal
  • jgebal
    jgebal Member Posts: 21 Blue Ribbon
    edited Feb 24, 2019 9:28AM

    Thank you @Vadim Tropashko-Oracle !!!

    This is really great help

  • jgebal
    jgebal Member Posts: 21 Blue Ribbon
    edited Feb 27, 2019 4:36AM

    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;

  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,249 Employee
    edited Feb 27, 2019 1:21PM Accepted Answer

    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)