This relates to Toad for Oracle; Beta 18.104.22.168.
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.