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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
which DB are you using ?
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.