7 Replies Latest reply on Oct 25, 2019 12:21 PM by thatJeffSmith-Oracle

    SQL Developer Format Option

    3974094

      Hi All,

       

      My current format is not good for case statement and analytical functions, can you help me fix in my desired format.

       

      Current Format -

       

      , CASE

                           WHEN stat_name = 'bytes sent via SQL*Net to dblink' THEN

                               value

                           ELSE

                               0

                       END AS sent

       

       

      Desired Format - It should align on WHEN, = and THEN.

       

      , CASE WHEN stat_name = 'bytes sent via SQL*Net to dblink' THEN value

           ELSE 0

         END AS sent

       

       

      Current Format -

       

      , nvl(decode(greatest(bytes_sent, nvl(LAG(bytes_sent) OVER(

          PARTITION BY ss.dbid, ss.instance_number

          ORDER BY ss.snap_id

      ), 0)), bytes_sent, bytes_sent - LAG(bytes_sent) OVER(

          PARTITION BY ss.dbid, ss.instance_number

          ORDER BY ss.snap_id

      ), bytes_sent), 0) bytes_sent_delta

       

       

      Desired Format -

       

      NVL (

                 DECODE (

                     GREATEST (bytes_sent,

                         NVL (

                             LAG (bytes_sent) OVER (PARTITION BY SS.dbid, SS.instance_number ORDER BY SS.snap_id),

                             0)),

                     bytes_sent,   bytes_sent

                                 - LAG (bytes_sent) OVER (PARTITION BY SS.dbid, SS.instance_number ORDER BY SS.snap_id),

                     bytes_sent),

                 0)    bytes_sent_delta,

        • 1. Re: SQL Developer Format Option
          Gaz in Oz

          To post how you want your desired format to appear, in this forum  when posting you need to use the "Use advanced editor" (top right) in your forum post...

          To change the format of the "Format (Ctrl-f7)" formatting in sqldev navigate to:

          Tools -> Preferences... -> +Format -> +Advanced format

          and fiddle about with the settings there.

          • 2. Re: SQL Developer Format Option
            thatJeffSmith-Oracle

            First thing, cycle through the various CASE IF THEN line break options to see if you can get closer.

             

            • 3. Re: SQL Developer Format Option
              3974094

              Thank You.

               

              Case one is solved now, I am good with the format. I truly appreciate your quick response.

               

              Now, help me correcting the format for Analytical functions. Please.

              • 4. Re: SQL Developer Format Option
                thatJeffSmith-Oracle

                pretty easily actually

                 

                • 5. Re: SQL Developer Format Option
                  Vadim Tropashko-Oracle

                  Here is how to remove some unwanted line breaks:

                   

                  simpleIndentConditions:

                  ...

                  | [node) file_specification

                  | [node) function_expression

                  | :breaksAfterSelectFromWhere &  [node) group_by_list

                  ...

                  | [node) object_view_clause

                  | [node) order_by_clause & ![node-1)  query_partition_clause &

                               ([node^) aggregate_function | [node^) analytic_function | [node^) analytic_clause | [node^^) analytic_clause) --<-- not to ident order by in the main query block

                  | :breaksAfterSelectFromWhere & [node) "ord_by_1desc" & [node^) order_by_clause & ![node^-1) query_partition_clause

                  | :breaksAfterSelectFromWhere & [node) order_by_clause[31,38) & [node^) order_by_clause

                  ...

                  | [node) prm_spec &       [node^) fml_part 

                  --| [node) query_partition_clause --& [node^) analytic_clause

                  | [node) range_values_clause

                  ...

                   

                   

                  _extraBrkBefore:

                  ...| [node) having_clause

                  | [node) order_by_clause & [node-1) query_partition_clause

                  | [node) row_limiting_clause

                   

                  Output:

                   

                  SELECT

                          nvl(

                              decode(

                                  greatest(bytes_sent,

                                           nvl(

                                               LAG(bytes_sent) OVER(PARTITION BY ss.dbid, ss.instance_number ORDER BY ss.snap_id), 0)

                                  ), bytes_sent,

                                  bytes_sent -

                                  LAG(bytes_sent) OVER(PARTITION BY ss.dbid, ss.instance_number ORDER BY ss.snap_id), bytes_sent), 0)

                      bytes_sent_delta

                  FROM

                      dual;

                  • 6. Re: SQL Developer Format Option
                    3974094

                    One more help how to align column alias i.e. when I have an column alias like AS column_name it goes in next line but I want it in same line and aligned.

                    • 7. Re: SQL Developer Format Option
                      thatJeffSmith-Oracle

                      please start new threads for new questions ... this is marked as Answered, so no one is really expecting new content here, nor is it being monitored