Thevagabond Thevagabond - 1 year ago 111
SQL Question

Calling a function that return a table with dates

I have a problem with a function.
I need to get a table with dates of a month, name of the day and the amound of weekdays in that month.
I already found some help here and adapted it to my need, but I can't get the function running/compiled
Here is what I have so far:

create or replace TYPE DATE_ROW AS OBJECT
MyDate DATE,
Dayname VARCHAR2(12),
Amount Integer

create or replace TYPE DATE_TABLE as table of DATE_ROW

create or replace FUNCTION myfinaldays (mydate date)
with this_month as (
select trunc(to_date('mydate'), 'mm')+level-1 dy
from dual
connect by level < (trunc(add_months(to_date('mydate'),1), 'mm')- trunc(to_date('mydate'), 'mm'))+1
FOR i IN 0 .. (select count(*) from this_month) LOOP
select (dy) Daydate,
to_char(dy, 'day'), Dayname,
( select count(*)
from this_month
where to_char(dy, 'dy') not in ('sat', 'sun')
) Amount
from this_month
where to_char(dy, 'dy') not in ('sat', 'sun')
and EXTRACT(day from to_date(dy)) = i;

pipe row (date_row(v_mydate,v_dayname, v_amount));

The function call would then be something like:

select * from date_table(cast(myfinaldays('01.02.12')));

I can only give one date as a parameter.

I hope someone can help me out here since this is slowly driving me mad.
Any ideas, examples, or thoughts would be greatly appreciated.


Ok here is an update with mybe some more helpfull infos to my problem:

This is working and my aim is to put this in a function so I can call it with 1 parameter:

with this_month as (
select trunc(to_date('01.02.12'), 'mm')+level-1 dy
from dual
connect by level < (trunc(add_months(to_date('01.02.12'),1), 'mm')- trunc(to_date('01.02.12'), 'mm'))+1
select (dy) mydate, (select count(*) from this_month) Days_in_month
, to_char(dy, 'day') Dayname
, ( select count(*) from this_month where to_char(dy, 'dy') not in ('sat', 'sun') ) Amount
from this_month
where to_char(dy, 'dy') not in ('sat', 'sun') ;

for the loop I added: 'and EXTRACT(day from to_date(dy))=i' at the end.

I added a date so you can see what I need in the end. If I write mydate instead and enter 01.02.12 as parameter after running it in the developer it still works.

Errors I get compiling:
- Error(10,1): PL/SQL: SQL Statement ignored

- Error(15,5): PL/SQL: ORA-00928: Keyword SELECT missing

- Error(22,8): PLS-00113: END-Definer 'LOOP' must complete 'myfinaldays' in row 1, comlumn 10

- Error(23,4): PLS-00103: Found the symbol "RETURN"

The errors are translated since my Oracle does not run english so I hope to have guessed it correctly.

Answer Source

You have a number of problems here.

  • You can't use a select directly in for i in 1..x (and it should be 1, not 0), you'd have to select into a local variable and use that as the end value.
  • You can't use with as an input to a for loop; but you can use a cursor there, as I have below.
  • The select inside the loop with get no-data-found when i represents a Saturday or Sunday (the 4th in this case, assuming you're running it for February).
  • Getting the count of all non-weekend rows for every row is inefficient, though it doesn't really matter for such a small amount of data.
  • You are referring to to_date('mydate'); mydate is already a date, and 'mydate' is a string that has no relation to it.

And maybe some other things too, like assuming the date format and locale, since to_char(x, 'dy') will not return sat or sun everywhere, but I'll assume this is going to be used in a restricted way so you don't care about that too much.

From trying to figure out what output you're expecting, I think this will work (with modified types to get the days_in_month you added to the original question):

create or replace type date_row as object
    mydate DATE,
    dayname VARCHAR2(12),
    days_in_month NUMBER,
    amount NUMBER

create or replace type date_table as table of date_row

create or replace function myfinaldays (mydate date)
return date_table pipelined deterministic is
    for r in (
        select *
        from (
            select dy as daydate,
                to_char(dy, 'day') as dayname,
                count(*) over () as days_in_month,
                sum(case when to_char(dy, 'dy') in ('sat', 'sun')
                    then 0 else 1 end) over () as amount
            from (
                select trunc(mydate, 'mm')+level-1 dy
                from dual 
                connect by level < (trunc(add_months(mydate, 1), 'mm')
                    - trunc(mydate, 'mm')) + 1
        where to_char(daydate, 'dy') not in ('sat', 'sun')
    ) loop
       pipe row (date_row(r.daydate, r.dayname, r.days_in_month, r.amount));
    end loop;
end myfinaldays;

(If you don't want days_in_month then you can move the sat/sun check up and use the count for amount instead of the sum(case). The outer select is currently filtering on the day of the week so you can count all days, but that's not an issue if you don't want that value).

Then call as:

alter session set nls_date_format = 'DD.MM.RR';
select * from table(myfinaldays(to_date('01.02.12', 'DD.MM.RR')));

-------- ------------ ------------- ----------
01.02.12 wednesday               29         21
02.02.12 thursday                29         21
03.02.12 friday                  29         21
06.02.12 monday                  29         21
29.02.12 wednesday               29         21

21 rows selected.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download