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
Thanksselect 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);
//Tineselect get_cost_rate(1,3,3) from dual;
? select get_cost_rate(1,3,3) from dual;
should rather result in 50, not in 10, don't you think?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.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]Christine Schnittker wrote: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.
is the same as