PL SQL Developer

tyruss8
tyruss8 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
which DB are you using ?
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
U can  do like this-

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

Open in new window

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

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
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
PL SQL Developer is used with Oracle, not SQL Server.

Are you working with Oracle, or with Microsoft SQL Server?
Test your restores, not your backups...
Top Expert 2016
Commented:
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

Author

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
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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

Author

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!

Author

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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial