Thread: Problem with PLSQL Developer
05-08-12, 10:12 #1Registered User
- Join Date
- Mar 2012
Unanswered: Problem with PLSQL Developer
I am sorry if I am starting this thread in the wrong section.
Moderator: if that is the case please move to the right place.
I am having a problem with PLSQL Developer version 8.0 and version 9.0.
I am running against Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
All my queries run smooth on PLSQL until I try the Oracle "with" (subquery factoring). It is used to fill in the blanks in a query and get rows returned (with zero) where there is no data. Other timestamps just return the amount of transactions at that time. If I do not use "with" I only get the records where there are transactions.
The query below returns the amount of transactions for each second, and (thanks to the "with") it returns 0 for the seconds with no transactions.
with dt_range as (
select to_date ('010512 14:21:00', 'ddmmyy HH24:mi:ss') as start_dt,
to_date ('020512 21:59:59', 'ddmmyy HH24:mi:ss') as end_dt
all_seconds as (
select start_dt + interval '1' second * level as second_counter
connect by level < (select (end_dt - start_dt) * 24 * 60 * 60 from dt_range)
select to_char(second_counter, 'dd-mon-yy - hh24:mi'),
from all_seconds s
left join ama.FLIGHT_SEGLEGTB mt on to_char(mt.upd_tms,'yyyy-mm-dd hh24.mi.ss') = to_char(s.second_counter,'yyyy-mm-dd hh24.mi.ss')
where s.second_counter between (select start_dt from dt_range) and (select end_dt from dt_range)
group by to_char(second_counter, 'dd-mon-yy - hh24:mi')
order by to_char(second_counter, 'dd-mon-yy - hh24:mi')
So there is a setting in my PLSQL Developer inhibiting the normal execution of my "with" (subquery factoring) query. I've been using PLSQL Developer intensively and have not experienced any kind of problems with execution of statements. It just does the job.
Would appreciate any kind of tips. I do not want to replace "with" I just wish to know why it answers "0 rows returned" in PLSQL Developer.
thanks in advance
Last edited by aircooled; 05-08-12 at 11:07.