All,
This is about Beta 13.0.0.14 of Toad for Oracle.
I have a query of 118 lines. When formatting, the formatter puts the AS clauses into different columns in stead of aligning them properly.
However, when I take only the first 11 lines and format those, the AS clauses are aligned properly. Rather strange, isn’t it?
Here’s the short one, formatted:
insert /*+ append /
into a_partprice_webshop
select /+choose */
pp.partno
, pp.pricelist
, max(pp.valid_from) as valid_from
, max(pp.price) keep (dense_rank last order by pp.valid_from) as price
, max(pp.unitprice) keep (dense_rank last order by pp.valid_from) as unitprice
, max(pp.limitdef) keep (dense_rank last order by pp.valid_from) as limitdef
, max(pp.margin) keep (dense_rank last order by pp.valid_from) as margin
from ehda.a_partprice pp
The AS clauses are aligned on column 74, which seems the logical thing to do in this query.
Now here’s the full query, formatted as well:
create table a_partprice_webshop_abe
as
select *
from a_partprice_webshop
where 1 = 0;
insert /*+ append /
into a_partprice_webshop
select /+choose */
pp.partno
, pp.pricelist
, max(pp.valid_from) as valid_from
, max(pp.price) keep (dense_rank last order by pp.valid_from) as price
, max(pp.unitprice) keep (dense_rank last order by pp.valid_from) as unitprice
, max(pp.limitdef) keep (dense_rank last order by pp.valid_from) as limitdef
, max(pp.margin) keep (dense_rank last order by pp.valid_from) as margin
from ehda.a_partprice pp
inner join ehda.a_pricegen pg
on pg.pricelist = pp.pricelist
and pg.valid_from = pp.valid_from
and pg.pricegen_status not in (1, 4)
where pp.valid_from <= sysdate + 1 -->Script rescheduled to 21:00 instead after 0:00
group by pp.pricelist, pp.partno
union all -->RRUB based on REUR:
select a.partno
, ‘RRUB’
, a.valid_from -->LL; This is valid_from from REUR and not RRUB, but works for the webshop somehow…
, a.price * 69
, a.unitprice
, a.limitdef
, a.margin
from ehda.a_partprice a
where a.valid_from = (select max(z.valid_from)
from ehda.a_partprice z
where a.partno = z.partno
and a.pricelist = z.pricelist
and z.valid_from <= sysdate + 1 -->Script rescheduled to 21:00 instead after 0:00
and not exists
(select 1
from ehda.a_pricegen b
where z.pricelist = b.pricelist
and z.valid_from = b.valid_from
and b.pricegen_status in (1, 4)))
and a.pricelist = ‘REUR’
union all -->UEUR based on the UMARK converted with the currencyrate from GBP to EUR (I-1307-0265)
select a.partno
, ‘UMARK’
, a.valid_from -->LL; This is valid_from from UMARK and not UEUR, but works for the webshop somehow…
, a.price * 1.138822458
, a.unitprice
, a.limitdef
, a.margin
from ehda.a_partprice a
where a.valid_from = (select max(z.valid_from)
from ehda.a_partprice z
where a.partno = z.partno
and a.pricelist = z.pricelist
and z.valid_from <= sysdate + 1 -->Script rescheduled to 21:00 instead after 0:00
and not exists
(select 1
from ehda.a_pricegen b
where z.pricelist = b.pricelist
and z.valid_from = b.valid_from
and b.pricegen_status in (1, 4)))
and a.pricelist = ‘UEUR’
union all -->PLEUMAR based on the PLGEN converted with the currencyrate from PLN to EUR (Jira OMS-187))
select a.partno
, ‘PLEUMAR’
, a.valid_from -->LL; This is valid_from from PLGEN and not PLEUMAR, but works for the webshop somehow…
, a.price * .237959261
, a.unitprice
, a.limitdef
, a.margin
from ehda.a_partprice a
where a.valid_from = (select max(z.valid_from)
from ehda.a_partprice z
where a.partno = z.partno
and a.pricelist = z.pricelist
and z.valid_from <= sysdate + 1 -->Script rescheduled to 21:00 instead after 0:00
and not exists
(select 1
from ehda.a_pricegen b
where z.pricelist = b.pricelist
and z.valid_from = b.valid_from
and b.pricegen_status in (1, 4)))
and a.pricelist = ‘PLGEN’
union all -->KGCSP0 based on the KGPRLCW converted with the currencyrate from EUR to PLN (Jira OMS-435))
select a.partno
, ‘KGCSP0’
, a.valid_from
, a.price * 4.2024
, a.unitprice
, a.limitdef
, a.margin
from ehda.a_partprice a
where a.valid_from = (select max(z.valid_from)
from ehda.a_partprice z
where a.partno = z.partno
and z.pricelist = ‘KGPRLCW’
and z.valid_from <= sysdate + 1
and z.valid_from >= (add_months((last_day(trunc(sysdate)) + 1), -2)))
and a.valid_from >= (add_months((last_day(trunc(sysdate)) + 1), -2))
and a.pricelist = ‘KGPRLCW’
union all --> based on the EXPEUR converted with a factor pricelist
select a.partno
, ‘IRLNGBP’
, a.valid_from
, a.price * .8781
, a.unitprice
, a.limitdef
, a.margin
from ehda.a_partprice a
where a.valid_from = (select max(z.valid_from)
from ehda.a_partprice z
where a.partno = z.partno
and a.pricelist = z.pricelist
and z.valid_from <= sysdate + 1 -->Script rescheduled to 21:00 instead after 0:00
and not exists
(select 1
from ehda.a_pricegen b
where z.pricelist = b.pricelist
and z.valid_from = b.valid_from
and b.pricegen_status in (1, 4)))
and a.pricelist = ‘EXPEUR’;
The formatter still seems to attempt to align on column 74, but two of the lines are longer than that, necessitating the AS clauses to shift further to the right.
So why are the columns aligned on column 12 in the full query, and on column 10 in the abridged version?
It looks like a bug to me (inconsistent behavior).
Does this qualify for fixing?