Link to home
Start Free TrialLog in
Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America

asked on

Function to return one result based on data in first query

How can I write a function to return the first results if the rosterdate is > SYSDATE-1 year and the results of the second query otherwise?

Query 1:
    WITH cte AS (
        SELECT ecode, license, liclbl, cstate, declined, rosterdate 
        FROM reg, cert, event, ctbl, rates, clients, (SELECT * FROM names WHERE names.us=1) states
        WHERE reg.rid=cert.reg.rid(+) 
          AND event.rid=reg.event.rid  
          AND reg.clientid=clients.clientid 
          AND cert.ctbl.rid=ctbl.rid(+) 
          AND rates.regabrev(+)=cstate 
          AND ctbl.cstate=states.st(+)  
          AND reg.clientid=:reg.clientid 
        ORDER BY ecode desc
    )
    SELECT license AS LICENSENUM, cstate AS STATEOFLICENSE, liclbl AS LABEL, rosterdate AS ROSTERDATE 
    FROM cte WHERE rownum <= 1 ORDER BY rownum;

Open in new window

Query 2:
    SELECT c.licnum LICENSENUM
         , c.licst STATEOFLICENSE
         , (SELECT lic_lbl FROM rates 
            WHERE rates.regabrev = c.licst) LABEL
         , SYSDATE AS ROSTERDATE
    FROM clients c 
    WHERE c.clientid = :rid

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What data type are you looking to return from our function?

Returning a cursor seems like a bit of overkill to return a single row.

If you are returning an user-defined record, XML or something similar, You run the first select and capture the results, check the date value and if necessary, run the second select.

If you want to return a cursor, I'm not sure I see a way without first querying the database to get the date.  I might be able to think up some UNION type magic with maybe a MAX wrapper to do it all in one query but it would involve executing both queries every time.
Avatar of Eddie Shipman

ASKER

Rather return pipelined table than ref cursor.
OK, then do the first select into the variables/record, check the date value and if necessary, issue the second query to replace the values.  then pipe the row.
OK, I think I might have it, just can't figure out how to define the %ROWTYPE needed for the select Into for the queries.

CREATE OR REPLACE FUNCTION F_GET_CORRECT_ROSTER_INFO(clientid in NUMBER)
    RETURN ROSTERINFO_TBL pipelined IS
    -- Not sure how to define the %ROWTYPE needed here...
    selROSTERDATE DATETIME;
BEGIN
    WITH cte AS (
        SELECT ecode, license, liclbl, cstate, declined, rosterdate 
        FROM reg, cert, event, ctbl, rates, clients, (SELECT * FROM names WHERE names.us=1) states
        WHERE reg.rid=cert.reg.rid(+) 
          AND event.rid=reg.event.rid  
          AND reg.clientid=clients.clientid 
          AND cert.ctbl.rid=ctbl.rid(+) 
          AND rates.regabrev(+)=cstate 
          AND ctbl.cstate=states.st(+)  
          AND reg.clientid=:clientid 
        ORDER BY ecode desc
    )
    SELECT license AS LICENSENUM, cstate AS STATEOFLICENSE, liclbl AS LABEL, rosterdate AS ROSTERDATE INTO selectedRow
    FROM cte WHERE rownum <= 1 ORDER BY rownum;

    -- Get the ROSTERDATE from selectedRow
    SELECT ROSTERDATE INTO selROSTERDATE FROM selectedRow;

    -- Check to see if it is more than one year ago
    IF(selROSTERDATE < ADD_MONTHS(TRUNC(SYSDATE), -12) THEN
        SELECT c.licnum LICENSENUM
             , c.licst STATEOFLICENSE
             , (SELECT lic_lbl FROM rates 
                WHERE rates.regabrev = c.licst) LABEL
             , SYSDATE AS ROSTERDATE INTO selectedRow
        FROM clients c 
        WHERE c.clientid = :clientid
    END IF;
    -- DO MY PIPELINING HERE WITH selectedRow
    LOOP
        PIPE ROW(ROSTERINFO_TBL_T(selectedRow.LICENSENUM, selectedRow.STATEOFLICENSE, selectedRow.LABEL, selectedRow.ROSTERDATE));
    END LOOP;
END;

Open in new window

You may not be able to use ROWTYPE.  You might have to declare an instance of ROSTERINFO_TBL and select into it.

Can you post the definition of ROSTERINFO_TBL?

>>          AND reg.clientid=:clientid

Inside a stored procedure, you don't use ':'.  I would also use a naming convention that indicates what variables are parameters and which ones are variables.  This will eliminate confusion inside the query where the parameter might be confused with a column name in the table.
Should have been easy enough to figure from the Pipe Row call...;-) BTW, this is a function not a procedure.

CREATE OR REPLACE TYPE ROSTERINFO_TAS OBJECT
(
    LICENSENUM VARCHAR(16),
    STATEOFLICENSE VARCHAR(2),
    LABEL VARCHAR(20),
    ROSTERDATE  DATE
)
/
 

CREATE OR REPLACE TYPE ROSTERINFO_TBL AS TABLE OF ROSTERINFO_T
/

Open in new window


I'm having issues with how AND where to setup the loop to pipeline the call.
Usually you do something like this:

for r in (
   SELECT...
)
LOOP
    PIPE ROW(...)
END LOOP;

Open in new window

My latest attempt...failure ;-(

CREATE OR REPLACE FUNCTION F_GET_CORRECT_ROSTER_INFO(clientid in NUMBER)
    RETURN ROSTERINFO_TBL pipelined IS
    selROSTERDATE DATETIME;
    selectedRow ROSTERINFO_TBL;
BEGIN
    WITH cte AS (
        SELECT ecode, license, liclbl, cstate, declined, rosterdate 
        FROM reg, cert, event, ctbl, rates, clients, (SELECT * FROM names WHERE names.us=1) states
        WHERE reg.rid=cert.reg.rid(+) 
          AND event.rid=reg.event.rid  
          AND reg.clientid=clients.clientid 
          AND cert.ctbl.rid=ctbl.rid(+) 
          AND rates.regabrev(+)=cstate 
          AND ctbl.cstate=states.st(+)  
          AND reg.clientid=clientid 
        ORDER BY ecode desc
    )
    SELECT license AS LICENSENUM
         , cstate AS STATEOFLICENSE
         , liclbl AS LABEL
         , rosterdate AS ROSTERDATE INTO selectedRow
    FROM cte                                                 -- Getting "Not Enough Values" on this line

    -- Get the ROSTERDATE from selectedRow
    SELECT ROSTERDATE INTO selROSTERDATE FROM selectedRow;   -- Getting "Table or view does not exist" on this line

    -- Check to see if it is more than one year ago
    IF(selROSTERDATE < ADD_MONTHS(TRUNC(SYSDATE), -12) THEN
        for r in 
        (
            SELECT c.licnum LICENSENUM
                 , c.licst STATEOFLICENSE
                 , (SELECT lic_lbl FROM rates 
                    WHERE rates.regabrev = c.licst) LABEL
                 , SYSDATE AS ROSTERDATE INTO selectedRow    -- Getting "Not Enough Values" on this line
            FROM clients c 
            WHERE c.clientid = clientid
        )
        -- DO MY PIPELINING HERE WITH selectedRow
        LOOP
            PIPE ROW(ROSTERINFO_TBL_T(selectedRow.LICENSENUM, selectedRow.STATEOFLICENSE, selectedRow.LABEL, selectedRow.ROSTERDATE));
        END LOOP;
    ELSE
        for r in                                             -- Getting "Table or view does not exist" on this line
        (
            SELECT * FROM selectedRow
        )
        -- DO MY PIPELINING HERE WITH selectedRow
        LOOP
            PIPE ROW(ROSTERINFO_TBL_T(selectedRow.LICENSENUM, selectedRow.STATEOFLICENSE, selectedRow.LABEL, selectedRow.ROSTERDATE));
        END LOOP;
    END IF;
END;

Open in new window


See where I marked where I am getting errors...
There are a few issues in there.

From the original post, it looks like you are always returning a single row so I'm not sure why you need a loop.

If you do, you can add it back in but not the way you were trying.

Here is a simplified version of what you posted.  I hard-coded the columns because I don't have your tables or data.

You should be able to add your queries back in.

drop TYPE ROSTERINFO_TBL;
drop TYPE ROSTERINFO_TAS;

CREATE OR REPLACE TYPE ROSTERINFO_TAS as OBJECT
(
    LICENSENUM VARCHAR(16),
    STATEOFLICENSE VARCHAR(2),
    LABEL VARCHAR(20),
    ROSTERDATE  DATE
)
/

show errors
 

CREATE OR REPLACE TYPE ROSTERINFO_TBL AS TABLE OF ROSTERINFO_TAS
/

show errors

CREATE OR REPLACE FUNCTION F_GET_CORRECT_ROSTER_INFO(clientid in NUMBER)
    RETURN ROSTERINFO_TBL pipelined IS
    selectedRow ROSTERINFO_TAS;
BEGIN
    SELECT rosterinfo_tas('license' , 'XX' , 'label' , sysdate)
	INTO selectedRow
    FROM dual;

    -- Check to see if it is more than one year ago
    IF selectedRow.rosterdate < ADD_MONTHS(TRUNC(SYSDATE), -12) THEN
            SELECT rosterinfo_tas('xxx' , 'xx' , 'lab' , SYSDATE)
			INTO selectedRow    -- Getting "Not Enough Values" on this line
            FROM dual;
    END IF;

    PIPE ROW(selectedRow);
END;
/

show errors

select f_get_correct_roster_info(1) from dual;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I like the second approach a little better..Kind of funny, though...
ROSTERINFO_TAS as OBJECT

Open in new window

should be
ROSTERINFO_T AS OBJECT

Open in new window

I noticed that after your post so I couldn't go back and edit it. I'll test this tonight...
Well, I finally got it working. I had left out a critical part that returned only one row other wise I got multiple rows.
Thanks Netminder...My PL/SQL isn't what it should be after working with Oracle for 5 yrs. ;-)