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?

The very first query is wrong already. `ROWNUM` is applied BEFORE `ORDER BY`. Your first query selects exactly **one** (pretty random) row from the table, it assigns to it `rownum = 1`, and then it orders this set of one row. Surely not what you thought it did! — , Mar 27 '18 at 16:26

Lukasz Szozda · Accepted Answer · 2018-03-27T16:29:01.980

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;

edited Mar 27 '18 at 16:29

answered Mar 27 '18 at 16:19

Lukasz Szozda

162,964
23
234
275

1

Thanks I tried both approaches and they work. May I ask why what I was attempting was incorrect? – Mars Mar 27 '18 at 16:25
1

@Ares Sure, the reason is very simple `SELECT * FROM table WHERE rownum = 1` => you will get one row before other conditions are applied and before ORDER BY – Lukasz Szozda Mar 27 '18 at 16:27
I think `MAX(id)` will return a NULL if there are no data found. So that might be preferable as one can then omit the `EXCEPTION` block. – David Faber Mar 27 '18 at 21:58

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

1 Answers1