formatter puts as clause on a separate line

Toad for Oracle - Beta
1

All,

I’m on Toad 12.11.0.37.

I’ve noticed some changes in the formatter:

  • it puts AS clauses on a separate line
  • in parameter lists, it puts the parameter name and the parameter values on separate lines

I find the new way of formatting undesirable (too many lines, reduces overview of the code)
I have searched for a formatting option to influence this behavior, but failed to locate
any such parameter. What have I missed?

Abe Kornelis

Sample query showing all AS clauses on a separate line:
insert /+append /
into stock_stats.sold_quantities_base_stock
select partno / generate one row for each part - per Country-Group /
, sales_type
, replacement_code
, include_country
, include_group
, main_compno
as compno_wrh
, main_compno
as compno_cus
, group_compno
as compno_group
, redundancy_code
, sum(tot_qty_economical)
as qty_on_stock_economical
, sum(tot_qty_physical)
as qty_on_stock_physical
, round(sum(avg_qty_1yr), 2)
as avg_qty_1yr / Add average stock quantity of all countries to obtain main-country’s total /
, round(sum(avg_qty_2yr), 2)
as avg_qty_2yr / Add average for each country to obtain country-group’s total /
, round(sum(avg_amt_1yr), 2)
as avg_amt_1yr
, round(sum(avg_amt_2yr), 2)
as avg_amt_2yr
, round(sum(avg_qty_1yr_economical), 2)
as avg_qty_on_stock_1yr_econ
, round(sum(avg_qty_1yr_physical), 2)
as avg_qty_on_stock_1yr_phys
, round(sum(avg_qty_2yr_economical), 2)
as avg_qty_on_stock_2yr_econ
, round(sum(avg_qty_2yr_physical), 2)
as avg_qty_on_stock_2yr_phys
, round(sum(avg_amt_1yr_economical), 2)
as avg_amt_on_stock_1yr_econ
, round(sum(avg_amt_1yr_physical), 2)
as avg_amt_on_stock_1yr_phys
, round(sum(avg_amt_2yr_economical), 2)
as avg_amt_on_stock_2yr_econ
, round(sum(avg_amt_2yr_physical), 2)
as avg_amt_on_stock_2yr_phys
, case
when sum(tot_qty_physical) = 0 then round(sum(total_prlcw) / sum(prlcw_count), 5) / default to unweighted average /
else round(sum(total_prlcw) / sum(tot_qty_physical), 5) / Weighted average according to stock level /
end
as avg_prlcw
, sum(tot_amt_economical)
as amt_on_stock_economical
, sum(tot_amt_physical)
as amt_on_stock_physical
, sum(orderlines)
as orderlines
, case
when min(mon_created) is null then p_earliest_period – for missing connections, assume connection has existed since OMS started
else min(mon_created)
end
as mon_created
, current_date
as date_inserted
, null
as date_updated
, mon_code
from (select ssd.partno / here we generate one row for each part - per Legal Entity /
, ssd.sales_type
, ssd.replacement_code
, ssd.include_country
, ssd.include_group
, ssd.main_compno
, ssd.group_compno
, ssd.redundancy_code
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.qty_economical
else 0
end
)
as tot_qty_economical
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.qty_physical
else 0
end
)
as tot_qty_physical
/*********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_1YR_PHYSICAL /
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_physical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_qty_1yr
/**********************************************************************************************************************************/
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_physical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_qty_1yr_physical
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_economical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_qty_1yr_economical
/**********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(ssd.qty_physical) / count(distinct ssd.mon_code)
as avg_qty_2yr / Calculate average over last 24 months /
/***********************************************************************************************************************************/
, sum(ssd.qty_physical) / count(distinct ssd.mon_code)
as avg_qty_2yr_physical
, sum(ssd.qty_economical) / count(distinct ssd.mon_code)
as avg_qty_2yr_economical
/***********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_physical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_amt_1yr
/**********************************************************************************************************************************/
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_physical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_amt_1yr_physical
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_economical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
)
as avg_amt_1yr_economical
/**********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(ssd.stock_value_physical) / count(distinct ssd.mon_code)
as avg_amt_2yr / Calculate average over last 24 months /
/**********************************************************************************************************************************/
, sum(ssd.stock_value_physical) / count(distinct ssd.mon_code)
as avg_amt_2yr_physical
, sum(ssd.stock_value_economical) / count(distinct ssd.mon_code)
as avg_amt_2yr_economical
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.stock_value_economical
else 0
end
)
as tot_amt_economical
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.stock_value_physical
else 0
end
)
as tot_amt_physical
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then 1
else 0
end
)
as orderlines
, sum(case / Calculate total prlcw only for current month */
when ssd.mon_code = 100 * p_year + p_month
then
case
when qty_physical = 0 then ext_prlcw
&n

2

Hello Abe,

I’m with you. The goal of the new AS positioning is to align all aliases in a single column to make it easier to look them up. In the prior formatting they could get aligned either towards the left, or somewhere to the right, just a bit messy at times. But what we have now is way to much of a good (?) thing.

I feel I should look for a way to end up with one single column of aliases but not aligned to the left as is the case today, but rather somewhere in the middle of the line such that most aliases can stay on the same line, and if not, then on a next line but in the same column. Here’s an example of what I mean (which is essentially your code with a few aliases moved to a new line or moved to the right):

         include_country,
         include_group,
         main_compno                         AS compno_wrh,
         main_compno                         AS compno_cus,
         group_compno                        AS compno_group,
         redundancy_code,
         SUM (tot_qty_economical)            AS qty_on_stock_economical,
         SUM (tot_qty_physical)              AS qty_on_stock_physical,
         ROUND (SUM (avg_qty_1yr), 2)        AS avg_qty_1yr /* Add average stock quantity of all countries to obtain main-country's total */
                                                           ,
         ROUND (SUM (avg_qty_2yr), 2)        AS avg_qty_2yr /* Add average for each country to obtain country-group's total */
                                                           ,
         ROUND (SUM (avg_amt_1yr), 2)        AS avg_amt_1yr,
         ROUND (SUM (avg_amt_2yr), 2)        AS avg_amt_2yr,
         ROUND (SUM (avg_qty_1yr_economical), 2)
             AS avg_qty_on_stock_1yr_econ,
         ROUND (SUM (avg_qty_1yr_physical), 2)
                                             AS avg_qty_on_stock_1yr_phys,
         ROUND (SUM (avg_qty_2yr_economical), 2)
             AS avg_qty_on_stock_2yr_econ,
         ROUND (SUM (avg_qty_2yr_physical), 2)
                                             AS avg_qty_on_stock_2yr_phys,
         ROUND (SUM (avg_amt_1yr_economical), 2)
             AS avg_amt_on_stock_1yr_econ,
         ROUND (SUM (avg_amt_1yr_physical), 2)
                                             AS avg_amt_on_stock_1yr_phys,
         ROUND (SUM (avg_amt_2yr_economical), 2)
             AS avg_amt_on_stock_2yr_econ,
         ROUND (SUM (avg_amt_2yr_physical), 2)
                                             AS avg_amt_on_stock_2yr_phys,
         CASE
             WHEN SUM (tot_qty_physical) = 0
             THEN
                 ROUND (SUM (total_prlcw) / SUM (prlcw_count), 5) /* default to unweighted average */
             ELSE
                 ROUND (SUM (total_prlcw) / SUM (tot_qty_physical), 5) /* Weighted average according to stock level */
         END
                                             AS avg_prlcw,
         SUM (tot_amt_economical)            AS amt_on_stock_economical,
         SUM (tot_amt_physical)              AS amt_on_stock_physical,
         SUM (orderlines)                    AS orderlines,
         CASE
             WHEN MIN (mon_created) IS NULL THEN p_earliest_period -- for missing connections, assume connection has existed since OMS started
             ELSE MIN (mon_created)
         END
             AS mon_created,
         CURRENT_DATE                        AS date_inserted,
         NULL                                AS date_updated,

Thoughts?

Can you please also provide me an example of what you don’t like with the parameter lists?

Thanks,
Andre

3

In addition, about the named parameter list, there was the following change:

Named parameters: On overflow the right side of the arrow is wrapped onto a new line.
Examples:

BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller => g_my_schema || ‘.’ || g_my_package || ‘.’ || l_my_routine,
p_msg_id => g_my_msg_prefix || ‘9110D’,
p_message => 'build_customer_msg returned ’ || COALESCE (l_errmsg2, ‘<*Null>’));
END IF;
END;

BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller =>
g_my_schema || ‘.’ || g_my_package || ‘.’ || l_my_routine,
p_msg_id =>
g_my_msg_prefix || ‘9110D’,
p_message =>
'build_customer_msg returned ’ || COALESCE (l_errmsg2, ‘<*Null>’));
END IF;
END;

BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller =>
g_my_schema
|| ‘.’
|| g_my_package
|| ‘.’
|| l_my_routine,
p_msg_id =>
g_my_msg_prefix
|| ‘9110D’,
p_message =>
'build_customer_msg returned ’
|| COALESCE (
l_errmsg2,
‘<*Null>’));
END IF;
END;

( Unfortunately this documentation didn’t come through, you should normally see it showing up with the next drop. )

It wraps the RHS to the next line in an all-or-nothing fashion.

FYI here’s the “old” format:

/* Formatted on 8/02/2017 18:50:17 (QP5 v5.300) */
BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller => g_my_schema
|| ‘.’
|| g_my_package
|| ‘.’
|| l_my_routine,
p_msg_id => g_my_msg_prefix || ‘9110D’,
p_message => 'build_customer_msg returned ’
|| COALESCE (l_errmsg2, ‘<*Null>’));
END IF;
END;

BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller => g_my_schema
|| ‘.’
|| g_my_package
|| ‘.’
|| l_my_routine,
p_msg_id => g_my_msg_prefix || ‘9110D’,
p_message => 'build_customer_msg returned ’
|| COALESCE (l_errmsg2, ‘<*Null>’));
END IF;
END;

BEGIN
IF LOWER (p_debug) = ‘debug’
THEN
utl_krg.msg_log.log_debug_msg (
p_caller => g_my_schema
|| ‘.’
|| g_my_package
|| ‘.’
|| l_my_routine,
p_msg_id => g_my_msg_prefix || ‘9110D’,
p_message => 'build_customer_msg returned ’
|| COALESCE (l_errmsg2, ‘<*Null>’));
END IF;
END;

Perhaps we should add an option to choose between both modes?

Andre

4

Andre,

I’ll restrict my self to the AS-clause. I’ll start a separate thread on the parameter lists.

I think you’re on the right track with your proposal. I would like an enhancement hat does align the AS clauses.
However, sometimes you have a list of items with AS clauses, and one item is a lot longer than most.
This would result in a lot of whitespace between most tem definitions and their AS clause.
Which makes it difficult to visually correlate the AS clause with the corresponding item definition.

To prevent this situation I can see two options:

  1. set a fixed maximum on the AS alignment column
  2. set a max indent between the shortest item definition and the AS alignment column

Option two gives more flexibility. Option one should be easier to implement, I guess.
Of course, for both options, care must be taken that the AS clause (including the alias name)
does not extend beyond the maximum line length. In that case either the alignment column
should be shifted to the left so that even the longest name is accommodated,
or the overflowing lines should spill over to the next source line.

Also, when an item definition is a case statement, the AS clause should
(if that is possible) be on the same line as the END statement,
not on a separate line by itself. This should help to keep the code compact
to enhance a proper overview of the code one is working on.

I’m aware this may take a few settings in the formatter options file.
Maybe it is desirable to have a switch to turn AS alignment on or off altogether?
Maybe the same switch should apply to operator and second operand in comparisons
(as used in WHERE and ON clauses) ?

If you’re planning to apply comparable formatting to comparisons, then I would
surely prefer to have a floating max indent. With nested queries a fixed AS alignment
column (or a fixed max) makes relatively little sense. I’d prefer a floating max indent
anyway (as described above under option 2).

With a floating max indent of 10 positions, the sample query I provided above would be formatted as follows:
insert /+append /
into stock_stats.sold_quantities_base_stock
select partno / generate one row for each part - per Country-Group /
, sales_type
, replacement_code
, include_country
, include_group
, main_compno as compno_wrh
, main_compno as compno_cus
, group_compno as compno_group
, redundancy_code
, sum(tot_qty_economical)
as qty_on_stock_economical
, sum(tot_qty_physical)
as qty_on_stock_physical
, round(sum(avg_qty_1yr), 2)
as avg_qty_1yr / Add average stock quantity of all countries to obtain main-country’s total /
, round(sum(avg_qty_2yr), 2)
as avg_qty_2yr / Add average for each country to obtain country-group’s total /
, round(sum(avg_amt_1yr), 2)
as avg_amt_1yr
, round(sum(avg_amt_2yr), 2)
as avg_amt_2yr
, round(sum(avg_qty_1yr_economical), 2)
as avg_qty_on_stock_1yr_econ
, round(sum(avg_qty_1yr_physical), 2)
as avg_qty_on_stock_1yr_phys
, round(sum(avg_qty_2yr_economical), 2)
as avg_qty_on_stock_2yr_econ
, round(sum(avg_qty_2yr_physical), 2)
as avg_qty_on_stock_2yr_phys
, round(sum(avg_amt_1yr_economical), 2)
as avg_amt_on_stock_1yr_econ
, round(sum(avg_amt_1yr_physical), 2)
as avg_amt_on_stock_1yr_phys
, round(sum(avg_amt_2yr_economical), 2)
as avg_amt_on_stock_2yr_econ
, round(sum(avg_amt_2yr_physical), 2)
as avg_amt_on_stock_2yr_phys
, case
when sum(tot_qty_physical) = 0 then round(sum(total_prlcw) / sum(prlcw_count), 5) / default to unweighted average /
else round(sum(total_prlcw) / sum(tot_qty_physical), 5) / Weighted average according to stock level /
end as avg_prlcw
, sum(tot_amt_economical)
as amt_on_stock_economical
, sum(tot_amt_physical)
as amt_on_stock_physical
, sum(orderlines) as orderlines
, case
when min(mon_created) is null then p_earliest_period – for missing connections, assume connection has existed since OMS started
else min(mon_created)
end as mon_created
, current_date as date_inserted
, null as date_updated
, mon_code
from (select ssd.partno / here we generate one row for each part - per Legal Entity /
, ssd.sales_type
, ssd.replacement_code
, ssd.include_country
, ssd.include_group
, ssd.main_compno
, ssd.group_compno
, ssd.redundancy_code
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.qty_economical
else 0
end
) as tot_qty_economical
, sum(case / calculate total only for data for current month /
when ssd.mon_code = 100 * p_year + p_month then ssd.qty_physical
else 0
end
) as tot_qty_physical
/*********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_1YR_PHYSICAL /
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_physical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_qty_1yr
/**********************************************************************************************************************************/
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_physical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_qty_1yr_physical
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.qty_economical
else null
end
)
/ count(distinct case / Count months with stock /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_qty_1yr_economical
/**********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(ssd.qty_physical) / count(distinct ssd.mon_code)
as avg_qty_2yr / Calculate average over last 24 months /
/***********************************************************************************************************************************/
, sum(ssd.qty_physical) / count(distinct ssd.mon_code)
as avg_qty_2yr_physical
, sum(ssd.qty_economical) / count(distinct ssd.mon_code)
as avg_qty_2yr_economical
/***********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_physical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_amt_1yr
/**********************************************************************************************************************************/
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_physical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_amt_1yr_physical
, sum(case / Calculate average over last 12 months /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.stock_value_economical
else null
end
)
/ count(distinct case / Count months with stock-value /
when ssd.mon_code > 100 * (p_year - 1) + p_month then ssd.mon_code
else null
end
) as avg_amt_1yr_economical
/**********************************************************************************************************************************/
/ TODO: this column could be either removed or kept for backward compatibility; to be superceeded by column AVG_QTY_2YR_PHYSICAL /
, sum(ssd.stock_value_physical) / count(distinct ssd.mon_code)
as avg_amt_2yr / Calculate average over last 24 months /
/***********************************************************************************************************************************/
, sum(ssd.stock_value_physical) / count(distinct ssd.mon_code)
as avg_amt_2yr_physical
, sum(ssd.stock_value_economical) / count(distinct ssd.mon_code)
as avg_amt_2yr_eco

5

Was there a conclusion on this issue?

I’d definitely prefer to be able to switch off the folding of AS clauses to a new line, as well as to try to place them aligned at all.

In many cases (such as the first part of Abe’s original example), the select columns are so simple that it makes more sense to just have the AS right after the column name/expression.

Just my 2 øre …

Best regards,

Niels

6

Hi Andre, I am trying to find template settings in TOAD 12.7.0.121 for example you gave above, but unfortunately, nothing worked yet. Can you share what needs to be done in TOAD 12.7.0.121 options to achive same?