All,
This relates to Toad for Oracle; Beta 13.0.0.7.
I have checked the formatter setting to align column aliases.
Then I reformatted a package containing the following cursor definition:
cursor c_main
is
select /*+ CHOOSE */
distinct ch.custno
, decode(xls.custno, '-', '4', '2')
agreetype
, '*'
coclass
, com.buunit
, xls.compno
, case
when xls.agreetype = '2' then pl.currency
when xls.agreetype = '4' then com.currency
else null -->Only '2' and '4' are allowed
end
currency
, 0
coobj
, nvl(xls.custdisc, '-')
custdisc
, nvl(ch.seqno, 1)
seqno
, null
agreedesc
, null
pricecodeah
, xls.employno
, nvl(nvl((select pers.employname
from ehda.pers
where pers.employno = xls.employno
)
, xls.employno
)
, '<NULL>'
)
employname
, com.default_stockno
stockno
, trunc(sysdate)
fromdate
, to_date('31-12-2049', 'dd-mm-yyyy')
todate
, 'J'
prchangecd
, null
cosalespgmid
, 'J'
coagreeallowmod
, null
shipbefore
, xls.agreevalue
, decode(agreedef, 1, 1, 2, 2, 99999)
agreedef -->Only 1 and 2 are allowed, else ORA-01401
, 'N'
qtydisccd -->For BNX always 'N'
, nvl(trim(xls.partno), '-')
partno -->Discount% on CLASS4, partno always '-'
, case
when xls.agreedef = 1
and xls.partno <> '-'
and xls.unitprice is null
then
(select unit
from ehda.part
where partno = xls.partno
)
else
xls.unitprice
end
unitprice
, xls.custno
new_custno
, nvl(trim(c4.class1), '-')
class1
, nvl(trim(c4.class2), '-')
class2
, nvl(trim(c4.class3), '-')
class3
, nvl(trim(c4.class4), '-')
class4
, null
class6
, trunc(xls.start_date)
start_date
, xls.delete_record
from ehda_krg.ins_upd_hn0606_xls xls
left outer join ehda.coagreehead ch
on ( ch.custno = xls.custno
and ch.agreetype = decode(xls.custno -->In case not filled
, '-', '4', '2')
and ch.compno = xls.compno -->LE33 uses sames custdisc as LE30
and ch.custdisc = nvl(xls.custdisc, '-')
)
left outer join ehda.a_cscubusiness csc on (xls.custno = csc.custno)
left outer join ehda.a_pricelist pl on (csc.pricelist = pl.pricelist)
left outer join ehda.s_class4 c4 on (c4.class4 = xls.class4)
join (select cmp.compno
, cmp.buunit
, cmp.default_stockno
, cmp.currency
from utl_krg.company_tbl cmp
where ( cmp.end_date is null
or cmp.end_date >= trunc(current_date)
)
and ( cmp.software_config_id = 'KRAMP-OMS'
or compno = 20
)) com
on ( xls.compno = com.compno
and nvl(ch.currency, com.currency) = com.currency -->LE20 has 2 headers PLN and EUR (Baltics), added join on currency with companies table (nvl for new headers)
)
where trunc(xls.start_date) <= trunc(sysdate) -- #apanov added
and xls.delete_record <> 'Y'
order by xls.custno -->needed for comparison <l_custno = r1.new_custno>
;
As I understand it the alias on the sixth column (“currency”) is not on the same line as the end of the case statement because it already is a multi-line statement.
Same for columns labeled employname and unitprice. This makes the aliases end up starting in column 27.
Then, since these are the longest definitions, all other aliases also align on column 27.
This looks pretty strange on all the short definitions, but is most clearly demonstrated on column 7, which is set to 0 and labeled as “coobj”.
The label goes onto a separate line, but is indented far enough to be completely to the right of the 0 value specified in the previous source line.
I’d like to make some improvement suggestions:
- after a case statement, there is no need to put the alias onto a separate line;
just put it right after the end - in the appropriate column that is. - Align all labels to column 60, which is after the longest case-less statement.
Or even better: look at the rightmost position used without the labels. This is col 69 inside two case statements. Then align labels to the right of that, starting col 71.
Not a single label needs to go onto a line of its own. At least not in this particular sample statement. - When some coldefs have their label on a separate line, do not force this setting onto all coldefs in the statement.