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
 

 

Select highest value row from two tables one having duplicat

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Fri Jul 23, 2010 4:09 pm    Post subject: Select highest value row from two tables one having duplicat
Reply with quote

Hi,

I have 2 tables TABLE1 & TABLE2. TABLE1 has colums COL_TB1_1, COL_TB1_2,COL_TB1_3,COL_TB1_4.

TABLE2 has columns COL_TB2_1, COL_TB2_2, COL_TB2_3, COL_TB2_4, COL_TB2_5.

Out of these columns COL_TB1_1 is the primary key of TABLE1 & COL_TB2_1,COL_TB2_2,COL_TB2_3 together forms a unique key for TABLE2,with COL_TB2_2 containing date & COL_TB2_3 containing time.

Now I want to select COL_TB1_1, COL_TB1_3, COL_TB1_4, COL_TB2_4 & COL_TB2_5, such that only the highest value of COL_TB2_2 & COL_TB2_3 is selected for each COL_TB2_1.

I need to do this using SPUFI. I have written the below query which is not working:

Code:
SELECT COL_TB1_1, COL_TB1_3, COL_TB1_4, COL_TB2_4, COL_TB2_5
FROM (SELECT COL_TB2_4, COL_TB2_5
           FROM TABLE2
           WHERE COL_TB2_1 LIKE 'ABC%'
            ORDER BY COL_TB2_2 DESC,COL_TB2_3 DESC
            FETCH FIRST 1 ROW ONLY) A
WHERE A.COL_TB1_1 = COL_TB2_1
AND     B.COL_TB1_2 IN ('01','02')

The above query is throwing an error code of -199 & error message:
Code:
ILLEGAL USE OF KEYWORD ORDER.  TOKEN ) UNION EXCEPT WAS EXPECTED


Kindly let me know what modifications shall I make for my query to work. A completely new query is also welcome.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 4:17 pm    Post subject:
Reply with quote

well, guess you have learned that you can not do an order by in a sub-select.

why don't you just simply join the tables using an ON clause,
do an order by
and then FETCH 1st only?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 4:40 pm    Post subject:
Reply with quote

or simply remove the order by and fetch first from the subselect
and put it on the main select?

and I would check an explain output.
would not be suprised if db2 made it a join, anyway
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Fri Jul 23, 2010 5:07 pm    Post subject:
Reply with quote

Hi dbzTHEdinosauer,

I took the example of using order by in sub-select from this forum & tried it out. The link is: http://ibmmainframes.com/viewtopic.php?t=7683
There is an info I missed. TABLE1 has a composite key. One of them is COL_TB1_1.

I want to select all the composite key of TBALE1 having specific value in COL_TB1_1.

If I do a join followed by order by clause & then fetch first 1 row only will relay me only 1 row which isn't my intention.

Had I written a COBOL with DB2, the logic would have been as follows:

Step1. Get required columns along with COL_TB2_1 from TABLE2 ordered by date & time values
Step2. Report all values of specific columns from TABLE1 where COL_TB1_1 = COL_TB2_1
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 9:45 pm    Post subject:
Reply with quote

maybe in your subselect,

select .......
where col? like 'abc5'
and timestamp(datecol, timecol) = max(timestamp(datecol,timecol))

??????
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jul 23, 2010 9:53 pm    Post subject:
Reply with quote

this time I'll shout it :
you can order by fetch first in a subselect in DB2 9 NFM
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 10:02 pm    Post subject:
Reply with quote

and when you don't have DB2 9 NFM?
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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts New DB2 tables creation kishpra DB2 3 Tue Feb 02, 2016 7:00 pm


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