This discussion is archived
8 Replies Latest reply: Dec 10, 2012 7:50 AM by 978751 RSS

return a value on a introduced data

978751 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you Peter, your solution works perfect. It was my mistake when I test it and integrate into a function..

    Best,
    Mario

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points