Results 1 to 1 of 1
  1. #1
    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 - 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
    from dual
    all_seconds as (
    select start_dt + interval '1' second * level as second_counter
    from dt_range
    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') = to_char(s.second_counter,'yyyy-mm-dd')
    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')
    What actually happens is that my PLSQL Developer answers "0 rows returned". I am puzzled because the same statement with the same db logging in with the same user logged in in a colleagues PLSQL Developer returns over 1000 rows. When I log in with Excel in my PC, Oracle returns also over 1000 rows nice and dandy.

    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 10:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts