Procedure to single select statement

[inspi]

Database is oracle 10g.
I need to write the following procedure in a single select statement:

CREATE OR REPLACE PROCEDURE DB.PARAM (p_recordset1 OUT SYS_REFCURSOR)
AS
USERID VARCHAR2 (12);
CURSOR USERNAME is
select distinct uf.USERID FROM DB.USERFUND uf where uf.USERID = upper('xyz');

BEGIN

open USERNAME;
fetch USERNAME into USERID;

if USERNAME%notfound then
OPEN p_recordset1 FOR
SELECT DISTINCT fm.SECCLASSID as "SECCLASSID" FROM DB.SECMAST fm ORDER BY fm.SECCLASSID;
else
OPEN p_recordset1 FOR
SELECT ff.SECCLASSID AS "SECCLASSID" FROM DB.USERSEC ff WHERE ff.USERID = upper('xyz');
end if;

END PARAM;

Basically the output should be,

If the username ‘xyz’ exists in the column USERID of table USERSEC then only those SECCLASSID’s which has username as ‘xyx’ should be displayed
If the username doesn’t exists in the column USERID of table USERSEC then the result should be all the SECCLASSID’s column values from SECMAST table.

Thanks in advance

 

[PHV]

One way:

SELECT SECCLASSID FROM DB.USERSEC WHERE USERID = 'XYZ'
UNION
SELECT SECCLASSID FROM DB.SECMAST WHERE NOT EXISTS(SELECT SECCLASSID FROM DB.USERSEC WHERE USERID = 'XYZ')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[inspi]

That worked perfectly...thanks a lot