How to interrupt Oracle query

[sem]

I have a need to stop Oracle query. In pl/sql my task looks like

declare
  lTestDate date;

begin
--moving through a cursor
  for f in (select /*+ first_rows*/
             id, prev_id, time_stamp
              from my_table
             order by id desc) loop

--checking some condition
    if f.prev_id <> f.id then
    
      select time_stamp
        into lTestDate
        from my_table
       where id = f.prev_id;
    
    else
      lTestDate := f.time_stamp;
    end if;
--stopping further processing  
    exit when lTestDate < trunc(sysdate);
  
  end loop;
end;

To explain the idea: I'm moving through some table by index and check some condition. When it becomes true I'm stopping. Is it possible in plain SQL query?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Dima, it's great to see a post from you! I hope you and the rest of your family are doing well.

As I read your code, it seems to me that your code would be a perfect use of Oracle's "CONNECT BY...START WITH..." SQL code. Let me know if I am incorrect.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sem]

Dave, thank you, we're all well, hope you're too.

Not so perfect as really all that "backup" records have the same prev_id (actually the id of the real record). And unique id from the same sequence.

Currenly to get id's of changed records I use

select prev_id
  from my_table t
 where id >
       (select id
          from (select /*+ index_desc(t t_pk_i)*/
                 id
                  from my_table t
                 where t.prev_id = t.id
                   and t.time_stamp < trunc(sysdate)
                    or t.prev_id != t.id
                   and trunc(sysdate) >
                       (select time_stamp from my_table t1 where t1.id = t.prev_id)
                 order by 1 desc)
         where rownum = 1)

And it works fine, but I hate how it scans index twice (once to get the last record changed yesterday and once more to get already scanned rows).

What I need is a kind of "stopkey", like ROWNUM<n.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Dima,

since you can't 'stop' in sql - your query either runs or it doesn't, can you give us a business requirement? That way we might be able to suggest alternatives.

If you're doing this as part of a big script, as I suspect you know, it is possible to simulate conditional logic in sql plus.

Have you considered the use of the model clause, because I believe that it can do what you want (i.e. look at prev_id) and produce a value that depends upon prev_id.

Regards

T

 

[ChrisHunt]

Something like this?

SELECT MAX(id)
FROM   my_table mt
LEFT OUTER JOIN my_table mt2
ON mt2.id = mt.prev_id
WHERE NVL(mt2.time_stamp,mt.time_stamp) < TRUNC(sysdate);

If mt.prev_id is never null, you can use an inner join for mt2 and dispense with the NVL() - just compare with mt2.time_stamp.

The above should produce the same answer as your PL/SQL example. Your SQL example has so many nested queries that I couldn't really follow it. However...

SELECT id
FROM  (SELECT id
       FROM   some_table
       WHERE  this = that
       ORDER BY id DESC)
WHERE  rownum = 1

appears to be a more cumbersome way of saying

SELECT MAX(id)
FROM   some_table
WHERE  this = that

Maybe you're just being too clever? (Or maybe I'm just not clever enough!)

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[taupirho]

Would this work I wonder?


select id,prev_id,ts1,ts2
from
(
select /*+ first_rows*/
id, prev_id, time_stamp ts1,
lag( time_stamp) over (partition by id order by id desc) ts2
from my_table order by id desc
)
where id = prev_id
and ts2 >= trunc(sysdate)



In order to understand recursion, you must first understand recursion.