Sub Query Problem



IBM's flagship relational database management system

Sub Query Problem

Postby gunesh » Thu Mar 07, 2013 11:35 pm

Hi

I am trying to fetch 6th max or 8th min record.
so created query but not working anyone help me that what is wrong with this query.

6 Max Record

SELECT MIN(SAL) FROM EMP WHERE SAL IN (SELECT SAL FROM EMP FETCH FIRST 6 ROWS ONLY ORDER BY SAL DESC)


PLEASE LET ME KNOW WHAT IS WRONG WITH THIS QUERY
gunesh
 
Posts: 4
Joined: Wed Mar 06, 2013 10:49 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Sub Query Problem

Postby Akatsukami » Fri Mar 08, 2013 12:10 am

It doesn't work, of course; you told us that yourself.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Sub Query Problem

Postby Robert Sample » Fri Mar 08, 2013 12:57 am

Does "not working", as you referred to it, mean:
-- the query executes but does not produce any results
-- the query executes but produces too few records
-- the query executes but produces too many records
-- the query executes but something else happened
-- the query does not execute but no message is produced
-- the query does not execute and a system abend occurs
-- the query does not execute and a non-zero return code is returned
-- something else you did not explain?
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: Sub Query Problem

Postby gunesh » Fri Mar 08, 2013 11:38 am

I am fresher in mainframe and looking for job.
Its my logic only i don't have mainframe db2 access so i can not check query with db2.
So i need you peoples help for looking job and build logic in mainframe.
And gain experience with you people.


Thanks and Regards

Gunesh Asatkar
gunesh
 
Posts: 4
Joined: Wed Mar 06, 2013 10:49 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Sub Query Problem

Postby enrico-sorichetti » Fri Mar 08, 2013 12:32 pm

to learn and exercise DB2 You should consider downloading and installing on Your PC

http://www-01.ibm.com/software/data/db2 ... nload.html
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Sub Query Problem

Postby GuyC » Fri Mar 08, 2013 2:53 pm

subselect syntax diagram:
>-select-clause--from-clause--+--------------+----------------->
                              '-where-clause-'

>--+-----------------+--+---------------+----------------------->
   '-group-by-clause-'  '-having-clause-'

>--+-----------------+--+--------------------+-----------------><
   '-order-by-clause-'  '-fetch-first-clause-'


first ORDER BY, then FETCH FIRST clause :
SELECT MIN(SAL) FROM EMP WHERE SAL IN (SELECT SAL FROM EMP ORDER BY SAL DESC FETCH FIRST 6 ROWS ONLY )
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post