Reputation: 745
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
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
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