CODEWITHSUNDEEP
sqloracle-databaseplsql
HardLeeWorking
HardLeeWorking

Reputation: 195

How to ignore a row when a null value is returned?

I have inherited some code that includes the following error handling:

if v_complete then 
  if v_response_time is null then
    raise_application_error(-20999, 'Response Time Null');
  end if;

update eventlog e
set e.responded = get_work(v_callback, v_response_time),
complete_flag='Y'
where  rowid = v_min_row;

end if;

Changes to the system mean that some cases are throwing up a NULL value, instead of raising an application error I would like it to ignore these rows. How can I change the raise_application_error to an ignore or skip?

Upvotes: 0

Views: 108

Answers (4)

Dave Kelly
Dave Kelly

Reputation: 129

If you want to update the event log even if the response time is NULL, then you could give v_response_time a default value when NULL. I typically use '1900-01-01 00:00:00.000'. this can be useful.

if v_complete then 
  --if v_response_time is null then
    --raise_application_error(-20999, 'Response Time Null');
 --end if;

 update eventlog e
set e.responded = get_work(v_callback, COALESCE(v_response_time,'1900-01-01 00:00:00.000')),
complete_flag='Y'
where  rowid = v_min_row;

end if;

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The simplest thing to do would be:

if v_complete AND v_response_time IS NOT NULL then
  update eventlog e
    set e.responded = get_work(v_callback, v_response_time),
        complete_flag='Y'
    where  rowid = v_min_row;
end if;

This may or may not satisfy your requirements - I'm not certain I understand what you're trying to do - but have a look and see if this does what you want.

Best of luck.

Upvotes: 0

Boneist
Boneist

Reputation: 23578

Sounds like you just need:

if v_complete then 

  update eventlog e
  set    e.responded = get_work(v_callback, v_response_time),
         complete_flag='Y'
  where  rowid = v_min_row
  and    v_response_time is not null;

end if;

Upvotes: 2

Tim Jasko
Tim Jasko

Reputation: 1542

You can filter the NULL items out of your result set entirely by adding

and (v_response_time is not null)

to the query's where clause

Upvotes: 2

Related Questions