1 2 Previous Next 17 Replies Latest reply: Feb 27, 2013 2:45 PM by user9542267 RSS

    help with empty values

    user9542267
      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
          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
            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
              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
                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
                  in that case 10 because it not matching for both.
                  Thanks for your help!
                  • 6. Re: help with empty values
                    Christine Schnittker
                    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
                      I think v_param1.
                      Thanks!
                      • 8. Re: help with empty values
                        Christine Schnittker
                        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
                          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
                            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
                              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
                                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
                                  >
                                  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
                                    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