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:
- set a fixed maximum on the AS alignment column
- 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