8 Replies Latest reply: Dec 10, 2012 9:50 AM by 978751 RSS

    return a value on a introduced data

    978751
      Hello,

      I'm trying to get the value of a column on a fix data which is not mentioned into the table!
      For example, I have this db:
      Data     Name     Taxable
      01/02/12     John      Y
      01/05/12     John Y
      01/09/12     John      N
      01/15/12     John      Y
      01/20/12     John      N
      01/28/12     John      Y

      I need to create a function, that will return the value of "taxable" on a introduced date considering also the value after that date!

      For example if I want to know the value of "taxable" for data 01/21/2012 it should return 'N'.
      I did next function but it's not looking to work proper :(

      create function taxable
      (p_data in date
      )
      begin
      begin
      select max(data)
      into w_data
      from table
      where w_data < p_data
      and p_data < data
      and taxable= 'Y'
      and count(taxable) <= 1;

      return 'Y';

      exception
      when others then
      return 'N';
      end taxable;

      Basically I want to see what's the taxable value at a date that I don't have into the table but with I know is the same with the value added at the last date inserted into the table.
      For 01/21/2012 it should return me the taxable value on 01/20/2012.

      Anyone can tell me what's the correct function syntax?

      Thank you!
        • 1. Re: return a value on a introduced data
          Peter Gjelstrup
          Hi, welcome to the forum.


          Here's one way:
          with test_data as (
             select to_date('01/02/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
             select to_date('01/05/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
             select to_date('01/09/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
             select to_date('01/15/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
             select to_date('01/20/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
             select to_date('01/28/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual)
          -- Query starts here (This is your function)
          select max(taxable) keep (dense_rank last order by data) from test_data
          where data <= to_date('01/21/12', 'mm/dd/yy)');
          Regards
          Peter
          • 2. Re: return a value on a introduced data
            978751
            Hello Peter,

            Thank you for your fast answer and for the welcome message :)

            Your solution is good, my problem is that I have thousands of records and also I may have a second variable for the name.
            I presented a small table just as example!
            Also I need to be a function because I need to call it in other procedure something like
            select * from table
            where taxabel ('John', 'p_data')= 'Y'

            Thanks a lot,
            Mario
            • 3. Re: return a value on a introduced data
              Peter Gjelstrup
              Hi Mario,

              Could you show your expected output - based on your already given test data" if you query "Give all Taxable for John".

              /
              Peter
              • 4. Re: return a value on a introduced data
                978751
                The output should be Y or N.

                Based on the function I've wrote I need to do some changes on it to work proper and when I call that function to know if one name is taxable or not.
                For example if the table I present we call it taxes and I write:
                select * from students ss, taxes ta
                where ss.student_name=ta.name
                and taxable ('p_name', 'p_data')='Y';

                Should return me the list with students that on a date that I insert are taxable.
                Hope this clear the things.

                Thanks a lot
                • 5. Re: return a value on a introduced data
                  Peter Gjelstrup
                  Hi Mario,

                  Still not 100% sure what you are asking. Where does 'p_data' come from in your example?

                  But, if you require some function solution then something like this:
                  create or replace function taxable(p_name taxes.name%type
                                                    ,p_data date)
                  return varchar2
                  is
                     retval varchar2(1);
                  begin 
                     select max(ta.taxable) keep (dense_rank last order by data)
                     into   retval
                     from   taxes ta
                     where  ta.data <= p_data
                     and    ta.name = p_name;
                  
                     return nvl(retval, 'N');
                  end taxable;
                  /
                  
                  
                  select ss.*
                  from students ss
                  where 'Y' = (select taxable (ss.name, :your_date) from dual);
                  You could also implment a view solution. This requires a row generator. Rob van Wijk has a nice blog post on what he calls Interval Based Rowgeneration.
                  http://rwijk.blogspot.dk/2007/11/interval-based-row-generation.html

                  I have taken his example 6 (Model clause) and adapted to your original test data:
                  SQL> with test_data as (
                     select to_date('01/02/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                     select to_date('01/05/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                     select to_date('01/09/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
                     select to_date('01/15/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                     select to_date('01/20/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
                     select to_date('01/28/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual)
                  -- Query starts here
                  select day data, Name, Taxable
                     from (select Data, Name, Taxable, lead(Data) over (partition by Name order by Data) -1 end_date from test_data)
                   model
                      partition by (Data, Name, Taxable)
                      dimension by (0 i)
                      measures (Data Day, end_date)
                      rules
                      ( Day[for i from 1 to end_date[0] - Day[0] increment 1] = Day[0] + cv(i)
                      )
                  order by Data
                         , Name
                         , taxable
                  
                  DATA      NAME TAXABLE
                  --------- ---- -------
                  02-JAN-12 John Y
                  03-JAN-12 John Y
                  04-JAN-12 John Y
                  05-JAN-12 John Y
                  06-JAN-12 John Y
                  07-JAN-12 John Y
                  08-JAN-12 John Y
                  09-JAN-12 John N
                  10-JAN-12 John N
                  11-JAN-12 John N
                  12-JAN-12 John N
                  13-JAN-12 John N
                  14-JAN-12 John N
                  15-JAN-12 John Y
                  16-JAN-12 John Y
                  17-JAN-12 John Y
                  18-JAN-12 John Y
                  19-JAN-12 John Y
                  20-JAN-12 John N
                  21-JAN-12 John N
                  22-JAN-12 John N
                  23-JAN-12 John N
                  24-JAN-12 John N
                  25-JAN-12 John N
                  26-JAN-12 John N
                  27-JAN-12 John N
                  28-JAN-12 John Y
                  
                  27 rows selected.
                  Hope either helps
                  Peter
                  • 6. Re: return a value on a introduced data
                    978751
                    Hi Peter,

                    Thank you for your interest in helping me!

                    The solution is not really what I need...for example, he function you wrote will return 'Y' for 01/21/2012 even if the last value that it should be returned is 'N' the value from 01/20/2012!

                    Regarding the row generator, is not a solution, I have information for 10 years, and for 5000 students, I can't use a row generator!

                    The solution what I was thinking is to count the values of 'Taxable' column until the introduced data and if is smaller than 1 then it should return 'Y' otherwise it should return 'N'
                    and also it should check the max (data) and if between max(data)-p_data , 'taxable' is 'Y' it should return the previous value, otherwise it should return 'N'..

                    I'm stuck in this if then else.. and the out up is not what I need..
                    What I think is close to the result I need is this function:
                    create function taxable
                    (p_data in date
                    )
                    begin
                    begin
                    select max(data)
                    into w_data
                    from table
                    where w_data < p_data
                    and p_data < data
                    and taxable= 'Y'
                    and count(taxable) <= 1;
                    return 'Y';

                    exception
                    when others then
                    return 'N';
                    end taxable;

                    What I was thinking is to count the max data into the db for a name(p_name) and then I get the p_data. I check the db for the last value of the 'taxable' between first date in db and the p_data and keep it, after that I check the value of 'taxable' between the max data in db and p_data and if the 'taxable' is 'N' I return the value of 'taxable' between first data in db and p_data, otherwise I return 'N'.

                    So what I have to use is just the data already into the db, nothing else generated extra as I've explained; also I need it to be a function as I call it in many other instances..
                    Thanks again and if you still have the patience to give me one more reply I really appreciate! :)

                    Best,
                    Mario
                    • 7. Re: return a value on a introduced data
                      Peter Gjelstrup
                      Hi Mario,
                      The solution is not really what I need...for example, he function you wrote will return 'Y' for 01/21/2012 even if the last value that it should be returned is 'N' the value from 01/20/2012!
                      Can't see that it return 'Y'
                      SQL> create table taxes as (
                         select to_date('01/02/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                         select to_date('01/05/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                         select to_date('01/09/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
                         select to_date('01/15/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual union all
                         select to_date('01/20/12', 'mm/dd/yy)') Data, 'John' Name, 'N' taxable from dual union all
                         select to_date('01/28/12', 'mm/dd/yy)') Data, 'John' Name, 'Y' taxable from dual)
                      /
                      Table created.
                      SQL> select max(ta.taxable) keep (dense_rank last order by data) is_taxable
                         from   taxes ta
                         where  ta.data <= date '2012-01-21'
                         and    ta.name = 'John'
                      /
                      IS_TAXABLE
                      ----------
                      N         
                      1 row selected.
                      Regarding the row generator, is not a solution, I have information for 10 years, and for 5000 students, I can't use a row generator!
                      I wouldn't count on that, and at minimum it depends on what you are trying to answer.
                      The solution what I was thinking is to count the values of 'Taxable' column until the introduced data and if is smaller than 1 then it should return 'Y' otherwise it should return 'N'
                      and also it should check the max (data) and if between max(data)-p_data , 'taxable' is 'Y' it should return the previous value, otherwise it should return 'N'..
                      Fairly convoluted description. Is it not simply that the last recording Y or N, is valid until a new recording is entered?
                      (Still think that is what suggested function is doing)

                      create function taxable
                      (p_data in date
                      )
                      begin
                      begin
                      select max(data)
                      into w_data
                      from table
                      where w_data < p_data
                      and p_data < data
                      and taxable= 'Y'
                      and count(taxable) <= 1;
                      return 'Y';

                      exception
                      when others then
                      return 'N';
                      end taxable;
                      What is w_data - you never mentioned that.

                      And please remove that when others.
                      So what I have to use is just the data already into the db, nothing else generated extra as I've explained;
                      No, you have not explained. And your topic even has "introduced"

                      But true, maybe that would be too advanced. I am beginning to feel that this is school work. If it is, it would be a good thing to mention in your original post. The kind of help you would be getting could be quite different.


                      Regards
                      Peter
                      • 8. Re: return a value on a introduced data
                        978751
                        Thank you Peter, your solution works perfect. It was my mistake when I test it and integrate into a function..

                        Best,
                        Mario