how to return the due date for payment starting at the next month

Question

I have built this function that must return me the first payment due date for a pledge based of that pledge id. there is a date for every pledge in my table and the payment happens at the first day of each month,starting from the month following the pledge date.I need to invoke it from anonymous block ...hope it's clear enough...

here is the function code

create or replace function DD_PAYDATE1_SF (

pledge_id dd_payment.idpledge%type)

return  date is 

payment_date  dd_payment.paydate%type;

begin 

select    paydate  into payment_date from dd_payment 
where dd_payment.idpledge = pledge_id;



return payment_date;

end  DD_PAYDATE1_SF;

I tried the trunc function but it seems to return me all the subsequent payments-as I receive this error when invoking the function "exact fetch returns more than requested number of rows"- while I need only the first one of them how can I solve it

Answer 1

You need to join to the table that has the pledge date (I'm guessing pledge), filter to only get payment dates after that, and use the MIN aggregate function to get the first date:

create or replace function dd_paydate1_sf (
  pledge_id dd_payment.idpledge%type)
return  date is 
  payment_date dd_payment.paydate%type;
begin 
  select min(d.paydate)
  into payment_date
  from pledge p
  join dd_payment d on d.idpledge = p.pledge_id
  and d.paydate > p.pledge_date
  where p.pledge_id = dd_paydate1_sf.pledge_id;

  return payment_date;
end dd_paydate1_sf;
/

SQL Fiddle.

Although it's questionable if the join and filter are necessary, as they imply you can have a payment before you pledge, which doesn't really make sense; so you could probably get the same effect just by adding MIN() to your original query.

This won't show the first date for a new pledge (one received this month) and will return null. If you wanted those as well, and your rule about the first of the following months is always true, you could ignore the payments table and just work out what it should be:

create or replace function dd_paydate1_sf (
  pledge_id dd_payment.idpledge%type)
return  date is 
  payment_date dd_payment.paydate%type;
begin 
  select trunc(p.pledge_date, 'MM') + interval '1' month
  into payment_date
  from pledge p
  where p.pledge_id = dd_paydate1_sf.pledge_id;

  return payment_date;
end dd_paydate1_sf;
/

The TRUNC(<date>, 'MM') gives you the first day of the month that date is in, and you can then add one to get the first day of the following month.

SQL Fiddle.

It depends if you want the first expected payment date, or the date that a first payment was actually made, assuming dd_payment is recording actual payments that have occurred.

---

To get and display one value in an anonymous block:

declare
    paydate date;
begin
    paydate := dd_paydate1_sf(104);
    dbms_output.put_line(to_char(paydate, 'DD/MM/YYYY'));
end;
/