Eddie Shipman
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:
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;
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
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.
ASKER
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;
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.
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.
ASKER
Should have been easy enough to figure from the Pipe Row call...;-) BTW, this is a function not a procedure.
I'm having issues with how AND where to setup the loop to pipeline the call.
Usually you do something like this:
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
/
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;
ASKER
My latest attempt...failure ;-(
See where I marked where I am getting errors...
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;
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I like the second approach a little better..Kind of funny, though...
ROSTERINFO_TAS as OBJECT
should be ROSTERINFO_T AS OBJECT
I noticed that after your post so I couldn't go back and edit it. I'll test this tonight...
ASKER
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. ;-)
Thanks Netminder...My PL/SQL isn't what it should be after working with Oracle for 5 yrs. ;-)
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.