Value of variable in Oracle with Select Into not resolving during procedure

Question

I have a timestamp column in one of my tables. I want to get all entries that are a minute after the very first entry in the table. The timestamp is chronological with respect to the primary id.

To this end I retrieve the first entry in the table and put the value of the time column into a variable. Then I add a minute to this value and create a new variable with the new value. Using this new variable I search for the entry that is less than the adjusted timestamp and retrieve the id of that entry.

So my three variables are v_time, v_adjusted and v_id.

When setting v_time I have the following query,

begin
    select time_of
    into v_time
    from MYTABLE where rownum=1
    order by id asc;
exception
    when no_data_found then
    v_time := null;
end;

For v_adjusted i simply do,

v_adjusted := v_time + 1 / 1440.00;

When setting v_id I have,

begin
    select id
    into v_id
    from MYTABLE where time_of < v_adjusted and rownum=1 
    order by id desc;
exception
    when no_data_found then
    v_id:= null;
end;

These operations do not return the correct result. The retrieved id is wrong. I can confirm by executing a query with the new timestamp which returns the correct id. As an example, in my table adding a minute should return id 19 but the value of v_id is 1.

Why am I getting the wrong result?

Answer 1

You need to use subquery:

begin
    SELECT id 
    INTO v_id
    FROM (select id
          from MYTABLE where time_of < v_adjusted
          order by id desc
    ) sub
    where rownum=1;
exception
    when no_data_found then
    v_id:= null;
end;

or simply max:

begin
    select MAX(id)
    into v_id
    from MYTABLE where time_of < v_adjusted;
exception
    when no_data_found then
    v_id:= null;
end;

EDIT:

With FETCH (Oracle 12c):

begin
    select id
    into v_id
    from MYTABLE where time_of < v_adjusted;
    ORDER BY id DESC
    FETCH FIRST 1 ROWS ONLY;
exception
    when no_data_found then
    v_id:= null;
end;