|
View previous topic :: View next topic
|
| Author |
Message |
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
Hi all,
I have executed the below query to find the nth max salary.
SELECT MAX(SAL)
FROM (SELECT DISTINCT(SAL)
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROWS ONLY);
I got the following error while executing the query.
| Code: |
DSNT408I SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE
DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM1 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 61 0 0 -1 223 507 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000003D' X'00000000' X'00000000' X'FFFFFFFF'
X'000000DF' X'000001FB' SQL DIAGNOSTIC INFORMATION
|
Please help me on this. |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales
|
|
|
|
| Have you read what the error code and message means from the manuals |
|
| Back to top |
|
|
nagesh54
New User
Joined: 20 Jun 2008
Posts: 30
Location: Hyderabad
|
|
|
|
| From the error message it is showing that DB2 is not in to full function mode to support functions used by you in the query. There might be DB2 version migration is going on and it may be in compatability mode. Please check with your DBA. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
there is no issue with the db2 version .... DO not use ORDER BY SAL DESC and FETCH FIRST 5 ROWS ONLY in your inline view ....
for finding nth max salry use the query
| Code: |
SELECT * FROM EMP T1
WHERE (SELECT COUNT(DISTINCT SAL) FROM
EMP T2 WHERE T2.SAL > T1.SAL) = n
|
n is 1 for 2nd max sal , 2 for 3rd and so on .... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Ashimer,
Cool new avatar 
d |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Thanks Dick ..started a new Nemo reef tank at home ...  .. |
|
| Back to top |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| ashimer wrote: |
there is no issue with the db2 version .... DO not use ORDER BY SAL DESC and FETCH FIRST 5 ROWS ONLY in your inline view ....
for finding nth max salry use the query
| Code: |
SELECT * FROM EMP T1
WHERE (SELECT COUNT(DISTINCT SAL) FROM
EMP T2 WHERE T2.SAL > T1.SAL) = n
|
n is 1 for 2nd max sal , 2 for 3rd and so on .... |
Hi Ashimer,
i have referred the below post to code the above query.
ibmmainframes.com/viewtopic.php?t=25170&highlight=
Do u mean that the query is wrong. |
|
| Back to top |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| Please let me know whether my Query(Query posted by Stodolas) really works or should i go for the Co-Related Query specified by Aishmer.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Is there some reason you have not run the query posted by Ashimer to see if it works for you?
Have you tested the original you posted - what happened? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| You dont use fetch first in an inline view ... correct me if i am wrong ... the query you posted is not working right ? you can tweak your query or else apply another solution ... there is no compulsion here ... the chioce is yours ...let us know after testing ... |
|
| Back to top |
|
|
nag
Warnings : 1
New User
Joined: 29 Jul 2005
Posts: 20
|
|
|
|
| ashimer wrote: |
| You dont use fetch first in an inline view ... correct me if i am wrong ... the query you posted is not working right ? you can tweak your query or else apply another solution ... there is no compulsion here ... the chioce is yours ...let us know after testing ... |
Thanks Aishmer for your reply..
Your query is working fine for me. |
|
| Back to top |
|
|
ranjith Kandimalla
New User
Joined: 14 Jan 2009
Posts: 5
Location: Trivandrum
|
|
|
|
Hi Guys,
i need query to retrieve 1st, 2nd &3rd max salary from table.
Thanks,
Ranjith. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
why don't you just :
| Code: |
select sal from emp
group by sal
order by sal desc
fetch first 3 rows only |
actually it is a new feature in DB2 9 to be able to order by and fetch first n in a subselect. Which is usefull just for case like this (if you need to join with other tables) |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
if you
- need the three salaries in a subselect
- are not yet in DB2 9 NFM
- don't want to calculate COUNT(DISTINCT SAL) for each row of emp
you could try this :
| Code: |
with tmp(n,msal) as
(select 1, max(sal) from emp
union all
select n+1,
(select max(sal) from emp
where sal < msal)
from tmp
where n < 3
)
select * from tmp |
|
|
| Back to top |
|
|
ranjith Kandimalla
New User
Joined: 14 Jan 2009
Posts: 5
Location: Trivandrum
|
|
|
|
| Thank u, i will try this. |
|
| Back to top |
|
|
|
|
