This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 27, 2013 12:45 PM by user9542267 RSS

help with empty values

user9542267 Newbie
Currently Being Moderated
Hi All,
I have a rate table as:
create table myRates (
rate_id number, 
rate_type number, 
param_1 number, 
param_2 number, 
rate_cost number);
Data for this table as:
insert into myRates(1, 1, null, null, 10);
insert into myRates(2, 1, 1, 1, 15);
insert into myRates(3, 1, 1, 2, 20);
insert into myRates(4, 1, 2, 1, 25);
insert into myRates(5, 1, 2, 2, 30);
insert into myRates(6, 1, null, 3, 40);
insert into myRates(7, 1, 3, null, 50);
Now I have a function to get the rate_cost
create or replace function get_cost_rate(v_type in number, v_param1 in number, v_param2 in number) return number is
  Result number :=0;
begin
   begin
   select rate_cost into Result
   from myRates 
   where rate_type = v_type
   and param_1 = v_param1
   and param_2 = v_param2;
   
   exception when no_data_found then
      select rate_cost into Result
      from myRates 
      where  rate_type = v_type
      and param_1 is null
      and param_2 is null
   end;
   return(Result);
end get_cost_rate;
How can i get the default value (null) when it doesn't find param1 or param2?
select get_cost_rate(1,6,7) from dual; -- Result 10
select get_cost_rate(1,6,3) from dual; -- Result 40
select get_cost_rate(1,3,7) from dual; -- Result 50
Thanks

Oracle 9.1
Edited by: user9542267 on Feb 27, 2013 12:49 PM

Edited by: user9542267 on Feb 27, 2013 12:52 PM

Edited by: user9542267 on Feb 27, 2013 1:05 PM
  • 1. Re: help with empty values
    636309 Newbie
    Currently Being Moderated
    Hi,
    I think you want to modify the exception handling section as follows.

    exception when no_data_found then
    result := NULL;
  • 2. Re: help with empty values
    user9542267 Newbie
    Currently Being Moderated
    Thanks for you quick reply and help.
    I think my question is not clear: I want to get the results that you see next to the select statements.
    If any of the parameters is not found in the table, it should pick the rate with null value.
  • 3. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    select rate_cost into Result
       from myRates 
       where rate_type = v_type
       and (param_1 = v_param1 OR (param_1 IS NULL AND v_param1 IS NULL))
       and (param_2 = v_param2 OR (param_2 IS NULL AND v_param2 IS NULL));
    or (a bit shorter)
    select rate_cost into Result
       from myRates 
       where rate_type = v_type
       and (param_1 = v_param1 OR NVL(param_1,v_param1) IS NULL)
       and (param_2 = v_param2 OR NVL(param_2,v_param2) IS NULL);
    or, if you know a value which isn't a valid value for the params in the table (-1 maybe?)
    select rate_cost into Result
       from myRates 
       where rate_type = v_type
       and NVL(param_1,-1) = NVL(v_param1,-1)
       and NVL(param_2 ,-1) = NVL(v_param2,-1);
    //Tine

    Edited by: Christine Schnittker on 27.02.2013 19:27
  • 4. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    Ah, just realize that this is not what you wanted for output.

    But in this case, with your example data, what would you expect when calling
    select get_cost_rate(1,3,3) from dual;
    ?
  • 5. Re: help with empty values
    user9542267 Newbie
    Currently Being Moderated
    in that case 10 because it not matching for both.
    Thanks for your help!
  • 6. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    Why not the rate for rate_id 6? v_param2 matches the 3 just fine. I don't care about v_param1, just use the default.
    Or maybe rather the rate for rate_id 7? Again, v_param1 matches the 3 just fine. Don't care about v_param2, use the default for that.

    Which of the defaults is the best?
  • 7. Re: help with empty values
    user9542267 Newbie
    Currently Being Moderated
    I think v_param1.
    Thanks!
  • 8. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    In this case,
    select get_cost_rate(1,3,3) from dual;
    should rather result in 50, not in 10, don't you think?

    You're currently contradicting yourself. I suggest you start again by defining your business rule around defaults very exactly ;)
    If you have done that, you will be able to write a function which returns the value your business rule defined.

    // Tine
  • 9. Re: help with empty values
    user9542267 Newbie
    Currently Being Moderated
    Correct Christine, the result value should be 50.
    I did a quick sample data to show my issue, i didn't realize about the 3 in both places.
    Thanks for your help.
  • 10. Re: help with empty values
    John Spencer Oracle ACE
    Currently Being Moderated
    If I am understanding the thread correctly, the rules, in order of precedence are:

    If both param_1 and param_2 match the passed parameters return that rate_cost
    If param_1 matches and param_2 is null then return that rate_cost
    If param_2 matches and param_1 is null then return that rate_cost
    If neither param_1 nor param_2 match then return the rate_cost with both null

    If that is a correct summary, then something along the lines of:
    SQL> create function get_cost_rate(v_type   in number,
      2                       v_param1 in number,
      3                       v_param2 in number) return number is
      4    Result number;
      5  begin
      6     select rate_cost
      7     into result
      8     from (select rate_cost,
      9                  row_number() OVER (order by case when param_1 = v_param1 and
     10                                                        param_2 = v_param2 then 1
     11                                                   when param_1 = v_param1 and
     12                                                        param_2 is null then 2
     13                                                   when param_2 = v_param2 and
     14                                                        param_1 is null then 3
     15                                                   else 4 end) rn
     16           from myrates
     17           where rate_type = v_type)
     18     where rn = 1;
     19     return Result;
     20  end get_cost_rate;
     21  /
    
    Function created.
    
    SQL> select get_cost_rate(1,6,7) from dual;
    
    GET_COST_RATE(1,6,7)
    --------------------
                      10
    
    SQL> select get_cost_rate(1,6,3) from dual;
    
    GET_COST_RATE(1,6,3)
    --------------------
                      40
    
    SQL> select get_cost_rate(1,3,7) from dual;
    
    GET_COST_RATE(1,3,7)
    --------------------
                      50
    
    SQL> select get_cost_rate(1,3,3) from dual;
    
    GET_COST_RATE(1,3,3)
    --------------------
                      50
    You probably want to add some error handling for no_data_found, but I have no idea what you may want there.

    John
  • 11. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    That is actually the point I wanted to make. Unless you define your business rule around defaults very carefully, so it doesn't depend on "nice" data, the whole process can give you grey hairs.
    //Tine

    PS:
    nvl(param_1, v_param1) = v_param1 
    is the same as
    (param_1 = v_param1 OR param_1 IS NULL)
    [edit: This is only true if v_param1 is not NULL itself. thx for the correction]

    PPS: Be careful ;)

    Edited by: Christine Schnittker on 27.02.2013 21:04
  • 12. Re: help with empty values
    Solomon Yakobson Guru
    Currently Being Moderated
    Christine Schnittker wrote:
    is the same as
    I didn't foolow this particular topic, but in general they are not the same. First will return NULL when both param_1 and v_param1 are null while second will return TRUE.

    SY.
  • 13. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    >
    If I am understanding the thread correctly, the rules, in order of precedence are:

    If both param_1 and param_2 match the passed parameters return that rate_cost
    If param_1 matches and param_2 is null then return that rate_cost
    If param_2 matches and param_1 is null then return that rate_cost
    If neither param_1 nor param_2 match then return the rate_cost with both null
    >

    Yes. That would have been what I wanted to see from the OP. As of his pre-last post before yours, there wasn't such a precedence.

    //Tine
  • 14. Re: help with empty values
    Christine Schnittker Explorer
    Currently Being Moderated
    Hi SY,

    I stand corrected. I made the error of assuming that the in parameters would not be NULL (right after I told OP not to assume..)

    Thx
    Tine


    ~PS: Of course, if we allow v_param1 or v_param2 to be NULL, that would make the business rule even more complicated. Does NULL "match" NULL?~ :D
    ~scnr~

    Edited by: Christine Schnittker on 27.02.2013 21:13
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points