Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting Error while executing the Max salary Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Getting Error while executing the Max salary Query
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: 8593
Location: Back in jolly old England

PostPosted: Fri Jul 17, 2009 4:03 pm    Post subject:
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    Post subject: Reply to: Getting Error while executing the Max salary Query
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    Post subject:
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

Site Director


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

PostPosted: Fri Jul 17, 2009 9:08 pm    Post subject: Reply to: Getting Error while executing the Max salary Query
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    Post subject:
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    Post subject:
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.

http://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    Post subject: Reply to: Getting Error while executing the Max salary Query
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

Site Director


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

PostPosted: Mon Jul 20, 2009 1:55 am    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: i need query to retrieve 1st, 2nd &3rd max salary from t
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: 1278
Location: Belgium

PostPosted: Mon Mar 22, 2010 1:07 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Mar 22, 2010 1:19 pm    Post subject:
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    Post subject: Thank u
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us