impractical alignment of parameter values in list of named parameters

All,

I’m on Toad 12.11.0.39.

The formatter seems to be trying to align the => signs in my list of named parameters.
But it is breaking lines when it should not.
E.g. it breaks p_debug => ‘Nodebug’ into two separate code lines :frowning:

This is the sample query:

select category_old as category_economical
, category_new as category_physical
, count() as aantal
from (select r.partno
, r.compno
, substr(r.category_with_repl_reason, 1, 1)
as category_new
, substr(stock_stats.summarize.determine_category( /
turnover = sales (of parts+kits) - returns, include turnover of replaced parts */
p_partno =>
r.partno
, p_synonym_of =>
r.synonym_of
, p_value_lines =>
coalesce(r.value_lines, 0) + coalesce(r.repl_value_lines, 0)
, p_qty_on_stock =>
coalesce(r.qty_on_stock_economical, 0) + coalesce(r.repl_qty_on_stock_economical, 0)
, p_turnover_12m =>
coalesce(r.qty_sold_1yr, 0)
+ coalesce(r.qty_uninvoiced, 0)
- coalesce(r.qty_returned_1yr, 0)
- coalesce(r.qty_uninvoiced_returns, 0)
+ coalesce(r.repl_qty_sold_1yr, 0)
+ coalesce(r.repl_qty_uninvoiced, 0)
- coalesce(r.repl_qty_returned_1yr, 0)
- coalesce(r.repl_qty_uninvoiced_returns, 0)
, p_turnover_24m =>
coalesce(r.qty_sold_2yr, 0)
+ coalesce(r.qty_uninvoiced, 0)
- coalesce(r.qty_returned_2yr, 0)
- coalesce(r.qty_uninvoiced_returns, 0)
+ coalesce(r.repl_qty_sold_2yr, 0)
+ coalesce(r.repl_qty_uninvoiced, 0)
- coalesce(r.repl_qty_returned_2yr, 0)
- coalesce(r.repl_qty_uninvoiced_returns, 0)
, p_qty_reserved =>
coalesce(r.qty_reserved, 0) + coalesce(r.repl_qty_reserved, 0)
, p_create_mon =>
case
when r.linetype = ‘G’
then
coalesce(r.created_global, 200301)
else
coalesce(r.created_psbase
, r.created_local
, r.created_global
, 200301
)
end
, p_current_period =>
r.mon_code
, p_debug =>
‘Nodebug’
)
, 1
, 1
)
as category_old
from results r
where r.mon_code = 201612
and r.linetype = ‘D’
and r.redundancy_code = ‘N’
and r.compno > 0)
group by category_old, category_new
order by category_old, category_new;

As you can see, some parameter values are calculated using a lengthy expression.
Those are formatted okay, but the short ones that should fit on a single code line
are broken into two lines as well.

I would like the => symbols to be aligned, but rather not this way!

Kind regards,
Abe Kornelis.

Abe,

This is an excellent example. The idea of the “new” formatting was:

1/ To leave more room for wide RHS (right hand side) constructs by wrapping them on a new line on one indent from the margin, so to delay RHS folding,
2/ Treat all parameters the same way to make it more consistent and readable.

Using the “old” formatting with default margin of 78 (your margin may be different) we get:

                         p_partno           => r.partno,
                         p_synonym_of       => r.synonym_of,
                         p_value_lines      =>   COALESCE (r.value_lines, 0)
                                               + COALESCE (r.repl_value_lines,
                                                           0),
                         p_qty_on_stock     =>   COALESCE (
                                                     r.qty_on_stock_economical,
                                                     0)
                                               + COALESCE (
                                                     r.repl_qty_on_stock_economical,
                                                     0),

The new formatting yields:

                         p_partno =>
                           r.partno,
                        p_synonym_of =>
                           r.synonym_of,
                        p_value_lines =>
                             COALESCE (r.value_lines, 0)
                           + COALESCE (r.repl_value_lines, 0),
                        p_qty_on_stock =>
                             COALESCE (r.qty_on_stock_economical, 0)
                           + COALESCE (r.repl_qty_on_stock_economical, 0),

How about this (preserving old if no overflow, using new when overflow) ?

                        p_partno           => r.partno,
                        p_synonym_of       => r.synonym_of,
                        p_value_lines      =>
                             COALESCE (r.value_lines, 0)
                           + COALESCE (r.repl_value_lines, 0),
                        p_qty_on_stock     =>
                             COALESCE (r.qty_on_stock_economical, 0)
                           + COALESCE (r.repl_qty_on_stock_economical, 0),

Perhaps we could switch between old and new with an option.

Andre

Andre,

I think your final example is what I’d like to see.

Also, this morning we discussed alignment of AS clauses on http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/32141/64675#64675
Maybe you could try to apply the same set of rules on both types of lists?
With a little luck, you’ll be able to get away with only a single set of formatter options, rather than multiple sets that are nearly identical.

I think this might apply to:

  • AS clauses in SELECT column lists
  • lists of named parameters
  • comparison operations on WHERE, HAVING, and ON clauses
  • aliases of tables/views in FROM and JOIN clauses

Best regards,
Abe

Also

  • assignments ?

The idea of a single set of options is appealing, however I’m not sure that the spacing of 10 for the AS clause may also be desirable for comparisons, for instance. Of course the less redundant options the better, but in these cases we are only dealing with four or five kinds of constructs, and there are always subtle differences.

But in general we’re talking about a same mechanism for all, and that’s a good thing.

Thanks,
Andre

Andre,

I’d been thinking in terms of SQL statements. So you’re right, I missed the assignments - but those already do align on the assignment operator…

Nevertheless, same story applies to those as well. And you’re right, even if we differ on the details, we do agree entirely on the general direction.

Kind regards,
Abe

Adding reference QP-2344 to add the required options to get the output we discussed.

Please refer to http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/34361/67664#67664 for further discussion.

Fixed in component QP5 version 5.319.