IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Getting Error while executing the Max salary Query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Fri Jul 17, 2009 4:01 pm
Reply with quote

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
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Jul 17, 2009 4:03 pm
Reply with quote

Have you read what the error code and message means from the manuals
Back to top
View user's profile Send private message
nagesh54

New User


Joined: 20 Jun 2008
Posts: 30
Location: Hyderabad

PostPosted: Fri Jul 17, 2009 4:06 pm
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 17, 2009 8:29 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Jul 17, 2009 9:08 pm
Reply with quote

Hi Ashimer,

Cool new avatar icon_wink.gif

d
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 17, 2009 9:24 pm
Reply with quote

Thanks Dick ..started a new Nemo reef tank at home ... icon_biggrin.gif ..
Back to top
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Sat Jul 18, 2009 2:35 am
Reply with quote

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
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Sun Jul 19, 2009 11:07 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 20, 2009 1:55 am
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jul 20, 2009 1:23 pm
Reply with quote

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
View user's profile Send private message
nag
Warnings : 1

New User


Joined: 29 Jul 2005
Posts: 20

PostPosted: Wed Jul 22, 2009 12:10 am
Reply with quote

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
View user's profile Send private message
ranjith Kandimalla

New User


Joined: 14 Jan 2009
Posts: 5
Location: Trivandrum

PostPosted: Mon Mar 22, 2010 12:49 pm
Reply with quote

Hi Guys,

i need query to retrieve 1st, 2nd &3rd max salary from table.

Thanks,
Ranjith.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 22, 2010 1:07 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 22, 2010 1:19 pm
Reply with quote

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
View user's profile Send private message
ranjith Kandimalla

New User


Joined: 14 Jan 2009
Posts: 5
Location: Trivandrum

PostPosted: Mon Mar 22, 2010 7:05 pm
Reply with quote

Thank u, i will try this.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top