This content has been marked as final.
Show 17 replies
-
1. Re: help with empty values
636309 Feb 27, 2013 5:59 PM (in response to user9542267)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 Feb 27, 2013 6:03 PM (in response to 636309)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 Feb 27, 2013 6:28 PM (in response to user9542267)
or (a bit shorter)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, 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 (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);
//Tineselect 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);
Edited by: Christine Schnittker on 27.02.2013 19:27 -
4. Re: help with empty values
Christine Schnittker Feb 27, 2013 6:34 PM (in response to Christine Schnittker)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 Feb 27, 2013 6:49 PM (in response to Christine Schnittker)in that case 10 because it not matching for both.
Thanks for your help! -
6. Re: help with empty values
Christine Schnittker Feb 27, 2013 7:11 PM (in response to user9542267)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 Feb 27, 2013 7:20 PM (in response to Christine Schnittker)I think v_param1.
Thanks! -
8. Re: help with empty values
Christine Schnittker Feb 27, 2013 7:33 PM (in response to user9542267)In this case,
should rather result in 50, not in 10, don't you think?select get_cost_rate(1,3,3) from dual;
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 Feb 27, 2013 7:38 PM (in response to Christine Schnittker)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 Feb 27, 2013 7:54 PM (in response to user9542267)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:
You probably want to add some error handling for no_data_found, but I have no idea what you may want there.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
John -
11. Re: help with empty values
Christine Schnittker Feb 27, 2013 8:05 PM (in response to user9542267)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:
is the same asnvl(param_1, v_param1) = v_param1
[edit: This is only true if v_param1 is not NULL itself. thx for the correction](param_1 = v_param1 OR param_1 IS NULL)
PPS: Be careful ;)
Edited by: Christine Schnittker on 27.02.2013 21:04 -
12. Re: help with empty values
Solomon Yakobson Feb 27, 2013 7:59 PM (in response to Christine Schnittker)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
SY. -
13. Re: help with empty values
Christine Schnittker Feb 27, 2013 8:01 PM (in response to John Spencer)>
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 Feb 27, 2013 8:15 PM (in response to Solomon Yakobson)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