-
Junior Member
User function
Need a user function that will return top 3 status by seqno desc
data looks like
key seqno status
1 1 r
1 2 s
1 3 p
1 4 x
The function has to go in a query.
I need 'xps' returned.
Please can any one help me to create this user function?
-
Expert Member
Re: User function
Use the following query
SELECT key,seqno,status
FROM
(
SELECT *
FROM tablename
WHERE status IN('X','P','S')
ORDER BY seqno
)
WHERE ROWNUM<=3
It will return top 3 records having status xps
-
Expert Member
Re: User function
To Return 'xps' then use
CREATE OR REPLACE function fun1 return varchar as
cursor c1 is SELECT status
FROM (
SELECT key,seqno,status,rank()
OVER (ORDER BY seqno DESC) EMPRANK
FROM test_1)
WHERE emprank <=3;
c1r c1%rowtype;
temp varchar2(25);
begin
for c1r in c1 loop
temp:=nvl(temp,' ')||c1r.status;
end loop;
return nvl(temp,' ');
dbms_output.put_line(nvl(temp,' '));
end;
To Return x,p,s Records then use:
SELECT key,seqno,status FROM (
SELECT key,seqno,status,rank()
OVER (ORDER BY seqno DESC) EMPRANK
FROM test_1)
WHERE emprank <=3;
-
Junior Member
Re: User function
The status codes are not constants.
The seqno could be =1 or > 1.
the table name is ChgDate.
MS2000 SQL
Very new to SQL!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules