Hi,
I think you want to modify the exception handling section as follows.

exception when no_data_found then
result := NULL;

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

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;

?

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?

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

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

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

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.

>
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

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