CODEWITHSUNDEEP
sqloracle-databaseplsql
John Wick
John Wick

Reputation: 745

How to have the variable be assigned 'null' if no results are being returned?

Currently have a variable called VALUE that needs to be assigned a numeric value so that the stored proc doesn't error out . Currently there is no data in the database for every hour so when it reaches a point where there is no data for the prior hour, the whole procedure errors out (since nothing is being returned). Is there a way I can have null assigned to VALUE if no results are returned? Below is my 'SELECT INTO' code:

SELECT VALUE
  INTO v_PRIOR__VALUE
  FROM VALUE V
 WHERE CODE = rCode
   AND TIME = rTIME - 1/24;

Upvotes: 0

Views: 64

Answers (2)

Littlefoot
Littlefoot

Reputation: 143163

Or, you could actually handle such a situation:

declare
  v_prior__value value.value%type;
begin
  SELECT VALUE
    INTO v_PRIOR__VALUE
    FROM VALUE V
   WHERE CODE = rCode
     AND TIME = rTIME - 1/24;
exception
  when no_data_found then
    null; -- v_prior_Value will remain NULL, as declared in the DECLARE section

  when too_many_rows then
    v_prior_Value := -1;  -- this is just an example; you'd handle it the way you want
end;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271241

If you are expecting 0 or one rows, then I think the simplest method is aggregation:

SELECT MAX(VALUE)
INTO v_PRIOR__VALUE
FROM VALUE V
WHERE CODE = rCode AND TIME = rTIME - 1/24;

An aggregation query with no GROUP BY (or HAVING) always returns exactly one row.

Upvotes: 2

Related Questions