Toad World® Forums

Weird alignment of column aliases

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:

  1. 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.
  2. 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.
  3. When some coldefs have their label on a separate line, do not force this setting onto all coldefs in the statement.

Kind regards,
Abe Kornelis