PL SQL - Resembling (!a?0:a) functionality

Question

Hi,

I have a simple query in a cursor

Cursor some_cursor IS
    select 
       sum(some_field) 
    from some_table table_1 
    where 
     table_1.TYPE =1       
     AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
     AND table_1.f2 = 962
     AND table_1.f3 = 41813;

Then i do

   fetch some_cursor into some_var;--some_var is of type Number, cursor is open

When I run this query, there's the chance that some_var will be NULL. In that case, id like it to take the 0 value;

Something like

    --C like pseudocode of what I want
   (some_cursor!=Null?(fetch some_cursor into some_var):(some_var:=0))

Is there a way to do this? I was thinking of rewriting the above query to

Cursor some_cursor IS
    select 
       sum(some_field),count(*) 
    from some_table table_1 
    where 
     table_1.TYPE =1       
     AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
     AND table_1.f2 = 962
     AND table_1.f3 = 41813;

and then writing

   fetch some_cursor into some_var,some_counter;

   if (some_counter = 0) then
   begin
       some_var :=0;
   end

but this implies rewriting 10 cursors (yes, not so many). Maybe plsql has a cleaner way.

Thanks in advance

Answer 1

Try:

SELECT NVL(SUM(some_field),0)
  from some_table table_1 
 where table_1.TYPE =1                                                 
   AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
   AND table_1.f2 = 962
   AND table_1.f3 = 41813;

Answer 2

You might correct a couple of other issues while you're there:

• Use implicit cursors instead of explicit
• Don't apply a function to a table column if you can avoid it.

thus:

Declare
   some_var Number;
Begin
   select Coalesce(sum(some_field),0)
   into   some_var
   from   some_table table_1   
   where  table_1.TYPE =  1                                                   
    AND   table_1.date >= date '2009-09-05' 
    AND   table_1.date <  date '2009-09-06' 
    AND   table_1.f2   =  962  
    AND   table_1.f3   =  41813;
End;