PL SQL Developer

Hello,
I have a broad question so please bare with me on this. I posted a question regarding how to calculate a working days using a holidays table. So now I have a function working.

But my question, how to call/execute this function so that I can run it against a table. (Kinda similar to MS Access queries work).
I've been searching the web but I just don't how what's the best practice. Example:
I have a function that accepts 2 dates and returns an integer number. (e.g., function_name (a date, b date) and returns the a number.
My table columns: id, start_date, end_date.
My expected results if possible: id, start date, end_date, "the number returned from the function"

Thank you.
tyruss8Asked:
Who is Participating?
 
Bill PrewCommented:
I'm assuming you are working with Oracle.

Your function is valid as coded, but keep in mind that for it to compile the HOLIDAYS table needs to exist.

For a test I ran the following and everything worked as expected, with the final SELECT returning 10 as a result.

CREATE TABLE HOLIDAYS (observed date);
/
INSERT INTO HOLIDAYS (observed) VALUES (TO_DATE('05/22/2017', 'mm/dd/yyyy'));
/
CREATE OR REPLACE FUNCTION work_days_between(p_date1 IN DATE, p_date2 IN DATE) 
   RETURN BINARY_INTEGER 
IS 
    v_holidays BINARY_INTEGER; 
    v_weekdays BINARY_INTEGER := 0; 
    v_date     DATE := TRUNC(p_date1); 
BEGIN 
    WHILE v_date <= p_date2 
    LOOP 
        IF TO_CHAR(v_date, 'Dy') NOT IN ('Sun', 'Sat') 
        THEN 
            v_weekdays := v_weekdays + 1; 
        END IF; 
        v_date := v_date + 1; 
    END LOOP; 
 
    SELECT COUNT(*) 
      INTO v_holidays 
      FROM HOLIDAYS 
     WHERE observed BETWEEN p_date1 AND p_date2; 
    RETURN v_weekdays - v_holidays; 
END;
/
SELECT * FROM HOLIDAYS;
/
SELECT work_days_between(TO_DATE('01/01/2017', 'mm/dd/yyyy'), TO_DATE('01/15/2017', 'mm/dd/yyyy')) AS Results FROM DUAL;
/

Open in new window


»bp
0
 
Pawan KumarDatabase ExpertCommented:
which DB are you using ?
0
 
Bill PrewCommented:
You can just use the function in a SQL statement, like:

select id, start_date, end_date, function_name(start_date, end_date) as newfield from table1;

Open in new window


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
U can  do like this-

SELECT id, start date, end_date,yourfunction(start date, end_date)  workingdays
from yourtable

Open in new window

1
 
tyruss8Author Commented:
I'm currently using PL SQL Developer;

My code is below, and when I run it in SQL Window, I get a message "Done", but I don't see the results.
I tried following:
1) replacing the select statement to with "Create table as" and shows "Done" but I don't see the table
2) I tried putting "/' after the function and when I run it, it gives me an error "ORA 06575 Package or Function in an invalid state"


CREATE OR REPLACE FUNCTION work_days_between(p_date1 IN DATE, p_date2 IN DATE)
   RETURN BINARY_INTEGER
IS
    v_holidays BINARY_INTEGER;
    v_weekdays BINARY_INTEGER := 0;
    v_date     DATE := TRUNC(p_date1);
BEGIN
    WHILE v_date <= p_date2
    LOOP
        IF TO_CHAR(v_date, 'Dy') NOT IN ('Sun', 'Sat')
        THEN
            v_weekdays := v_weekdays + 1;
        END IF;
        v_date := v_date + 1;
    END LOOP;

    SELECT COUNT(*)
      INTO v_holidays
      FROM HOLIDAYS
     WHERE observed BETWEEN p_date1 AND p_date2;
    RETURN v_weekdays - v_holidays;
END;
--/   >>> Tried add as an option, but giving me "invalid state" error...


select t.subscriber_id, t.elig_start_date, t.elig_end_date, work_days_between(t.elig_start_date, t.elig_end_date) newfield
from table t;

Thanks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
PL SQL Developer is used with Oracle, not SQL Server.

Are you working with Oracle, or with Microsoft SQL Server?
0
 
tyruss8Author Commented:
To: Scott McDaniel
I'm working with Oracle.

To: Bill Prew
Yes I do have HOLIDAYS table and I wrote tried your code and it is giving me an error "ORA-06575: Package or function "WORK_DAYS_BETWEEN is an invalid state"

Thanks
0
 
Bill PrewCommented:
If that function is in the schema now I would try removing it, then re-adding it, maybe it got messed up in earlier activities.


»bp
0
 
tyruss8Author Commented:
To Bill Prew:
By you validating my code actually pointed me to the right directions. My oversight was "observed ", where in my Holidays table I called the column "HOL_DATE". From here I was able to run the function and use it in a query which produced my desired results. Thank you!
1
 
tyruss8Author Commented:
Thanks Bill Prew appreciate it.

Although Pawan Kumar also assisted with my initial question, I end up giving all the points to Bill Prew because I didn't know how to divide the score 750 (Bill Prew)/250 (Pawan Kumar).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.