Beautifier: Factored subquery

[jschmied]

Hi!

The Beautifier doesnt understand this syntax:

with maxpk as (select max(pk) as pk from event_log t where event_text like '%Gleichamberg%')
SELECT * FROM event_log e WHERE e.pk BETWEEN (SELECT pk FROM maxpk) - 1 AND (SELECT pk FROM maxpk) + 1

greetings

Juergen

 

[Marco Kalter]

We'll fix it.

 

[mhthomas]

Originally posted by jschmied:
Hi!

The Beautifier doesnt understand this syntax:

with maxpk as (select max(pk) as pk from event_log t where event_text like '%Gleichamberg%')
SELECT * FROM event_log e WHERE e.pk BETWEEN (SELECT pk FROM maxpk) - 1 AND (SELECT pk FROM maxpk) + 1

greetings

Juergen

I don't get it. How can we assume the beautifier is not working? I tested two cases below successfully:

create or replace procedure foo as
ln number(8);
begin
with maxpk as(
select max(pk) as pk
from event_log t
where event_text like '%FOO%')
select *
into ln
from event_log e
where e.pk between (
select pk from maxpk) - 1 and
(select pk from maxpk) + 1;
end;

The above statement "beautified" in a Program Window. I also highlighted the SQL below in a SQL Window and ran "beautify". It worked fine.

with maxpk as(
select max(pk) as pk
from event_log t
where event_text like '%FOO%')
select *
from event_log e
where e.pk between (
select pk from maxpk) - 1 and
(select pk from maxpk) + 1;

Is this fixed? Or, did I misunderstand the poster's problem?

 

[jschmied]

I just tried with the version 7.0.3.1123

-----
with tis_data as (select aenderungsart, ereignis_date, external_key_fk from ereignis e, ereignis_tis et where e.ereignis_pk = et.ereignis_fk)
SELECT *
FROM tis_data d1, tis_data d2
WHERE d1.aenderungsart = 2
AND d2.aenderungsart = 3
AND d1.ereignis_date > d2.ereignis_date
AND d1.external_key_fk = d2.external_key_fk
-----

The beautifier is still not working in the SQL-Window (test-window and SQL-window with begin/end as a plsql-block works)

Thanks!

 

[Marco Kalter]

That is correct. Support for the WITH statement will be added in 7.1.