PL/SQL Function to return data between dates

Question

im having trouble creating a function..

I want the function to find what rent a certain account had at a certain date.

The function takes 2 parameters rentacc (number) and rentdate (varchar2)

create or replace function get_rent(rentacc in number,rentdate in varchar2)
return number
as
atype number :=rentacc
begin
if atype =1 
then
select "RATE" from "RENTCHANGE" where TO_DATE(rentdate, 'YYYY-MM-DD') >= TIME or TO_DATE(rentdate, 'YYYY-MM-DD') <=TIME;
else return -1;
end if;
end get_rent;

This is my table rentchange

ID         ACOUNT      RATE       TIME     
---------- ---------- ---------- ----------
       123          1        ,58 2013-07-09
       124          1        ,69 2013-09-02
       125          1       1,78 2013-10-07
       126          1        2,7 2013-10-17

select function_name(1,20131010)
from dual;

would return

function_name
-------------------------  
1,78

If someone has any advice i would appreciate it alot. Thanks.

are you sure the function is the one listed ?

BigMike
– BigMike

2015-11-30 11:36:08 +00:00
Commented Nov 30, 2015 at 11:36 — BigMike
– BigMike, Commented Nov 30, 2015 at 11:36
Not sure if i follow

Erskan
– Erskan

2015-11-30 12:11:24 +00:00
Commented Nov 30, 2015 at 12:11 — Erskan
– Erskan, Commented Nov 30, 2015 at 12:11

Boneist · Accepted Answer · 2015-11-30 12:13:35Z

2

Your question is confusing, since your example data and your function use different column names etc.

Anyway, here's a SQL statement that should help you see how you could amend the query in your function:

with rentchange as (select 123 id, 1 account, .58 rate, to_date('09/07/2013', 'dd/mm/yyyy') time from dual union all
                    select 124 id, 1 account, .69 rate, to_date('02/09/2013', 'dd/mm/yyyy') time from dual union all
                    select 125 id, 1 account, 1.78 rate, to_date('07/10/2013', 'dd/mm/yyyy') time from dual union all
                    select 126 id, 1 account, 2.7 rate, to_date('17/10/2013', 'dd/mm/yyyy') time from dual)
-- end of mimicking the rentchange table with data in it. See SQL below:
select rate
from   (select id,
               account,
               rate,
               time start_time,
               lead(time, 1, sysdate) over (partition by account
                                            order by time) end_time
        from   rentchange)
where  start_time <= to_date('10/10/2013', 'dd/mm/yyyy')
and    end_time > to_date('10/10/2013', 'dd/mm/yyyy');

      RATE
----------
      1.78

This uses the lead() analytic function to pull information about the next row's date (or, if there's no next row, use the current time), which then gives you a date range you can query between.

answered Nov 30, 2015 at 12:13

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Erskan Over a year ago

But if i would to insert 100 more rows into my table i have to change my function. This isnt optional is it?

Boneist Over a year ago

@ErikRehn If you're talking about the rentchange subquery that I used to generate data to run in the main bit of sql, then I hope you realise that you wouldn't need it. Since you haven't adequately explained what your function is supposed to do and how it will be used, it's almost impossible to say whether you'd need to change it or not. I would argue that if you're only going to do the select for one specific account, then you're probably not designing it well in the first place!

Erskan Over a year ago

so where you declare start and stop_time i would instead use my parameter?

Boneist Over a year ago

um, no; those are columns in the query; it's the to_date('10/10/2013', 'dd/mm/yyyy') which would be replaced by the parameter.

August Wande · Accepted Answer · 2015-12-03 10:39:30Z

0

This might be what you need, a bit more simplified compared to previous posts!

create or replace function get_ränta(
p_kontotyp in number,
p_datum in varchar2)
return number
is
v_svar number(5,2);
begin


select ränta into v_svar 
from ränteändring 
where tid = (select max(tid) from ränteändring
             where tid <= to_date(p_datum, 'yyyy,mm,dd')
             and ktyp = p_kontotyp);

return v_svar;
exception
when no_data_found then
return -1;
end;

answered Dec 3, 2015 at 10:39

August Wande

161 bronze badge

1 Comment

Erskan Over a year ago

Wow! Very nice. Great job

Collectives™ on Stack Overflow

PL/SQL Function to return data between dates

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related