2 Replies Latest reply on Feb 3, 2020 8:08 PM by Vadim Tropashko-Oracle

    19.4 - Formatting of partition by clause with 1 comma per line

    4156062

      I am using SQL Developer 19.4.0.354 with Default Settings.

       

      Changing the new "commas per line in procedures" option to 1 results in weird behavior for partition by clauses:

       

      SELECT
          prod_id,
          time_id,
          promo_id,
          channel_id,
          unit_cost,
          unit_price,
          SUM(unit_price) OVER(PARTITION BY prod_id, promo_id,
                                            channel_id) AS sum_unit_price
      FROM
          costs;
      

       

      How can I change this so the line break occurs after "prod_id", i.e. after the first comma?

        • 1. Re: 19.4 - Formatting of partition by clause with 1 comma per line
          thatJeffSmith-Oracle

          Uncheck the preference for linebreak on keywords

          • 2. Re: 19.4 - Formatting of partition by clause with 1 comma per line
            Vadim Tropashko-Oracle

            This bug has been fixed for 20.1... Retrofit to 19.4:

             

            breakAfterEvery5thArg: commasInProc

            ->{

                argNum++;  

                var proc = tuple.get("procedureCall");   

                var name = target.src[proc.from].content;

                var dot = target.src[proc.from+1].content;

                if( dot == '.' )

                name = name + '.' + target.src[proc.from+2].content;

                if( proc != priorProc ) {

                    argNum = 1;

                    priorProc = proc;

                } /*else*/ {

                    var breakEvery5thArg = struct.getCommasPerLine();

                    if( argNum%breakEvery5thArg == 0 ) {

                        var node = tuple.get("node");

                        var procIndent = struct.getNewline(proc.from);

                        if( procIndent == null )

                            procIndent = "\n";

                        var pad = new java.lang.StringBuilder();

                        var i;

                        for( i = 0; i <= name.length(); i++ ) 

                            pad.append(" ");

                        if( struct.getBoolBind("breaksAfterComma") )  

                            struct.putNewline(node.to, procIndent+pad.toString());

                        if( struct.getBoolBind("breaksBeforeComma") )

                           struct.putNewline(node.from, procIndent+pad.toString());

                    }       

                }

            }

             

            Output:

            SELECT

                prod_id,

                time_id,

                promo_id,

                channel_id,

                unit_cost,

                unit_price,

                SUM(unit_price) OVER(PARTITION BY prod_id,

                                                  promo_id,

                                                  channel_id) AS sum_unit_price

            FROM

                costs;