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

APPROPRIATE SELECT QUERY


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

New User


Joined: 20 Jul 2009
Posts: 23
Location: Mumbai

PostPosted: Tue Jun 29, 2010 4:52 pm
Reply with quote

Hi Guys

My table has 4 columns,i.e-
Party Key,Seq Num,End Date,Received date.
I am moving some value to party key and fetching the max(end date)

Select end date
from table T
where party key = num
and recvd date =(Select max(recvd date)
from table t
where party key = num)
and end date = (Select max(end date)
from table t
where party key = num)

In some cases for a particular party key the recvd date as well as the end date are same.So it's giving me -811 error.For all these rows the seq num
is only different.How can i modify my query to pick only one of the multiple rows which basically have same values in all the columns except seq num column.

Thanks in advance...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 29, 2010 5:05 pm
Reply with quote

so, what does the seqnum (within party key) signify?
especially, since the only difference in rows is the seqnum.

also, what is primary key?
Back to top
View user's profile Send private message
JnanaR

New User


Joined: 20 Jul 2009
Posts: 23
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:12 pm
Reply with quote

Primary key is Party Key+Seq Num.
Seq number is not provided.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 29, 2010 5:26 pm
Reply with quote

how many times do I have to ask?

Quote:
so, what does the seqnum (within party key) signify?
especially, since the only difference in rows is the seqnum.


why do you have rows, where the only difference is seqnum?
Back to top
View user's profile Send private message
JnanaR

New User


Joined: 20 Jul 2009
Posts: 23
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:40 pm
Reply with quote

I can't really tell the significance of seq num here as nothing was mentioned about seq num in specifications.
Also I am using the table from test region.The data could be modified.
But the objective is to fetch the latest end date for a particular party key
with latest received date.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jun 29, 2010 5:47 pm
Reply with quote

1) what happens if you have the following

Code:
PartyKey    Seq recvd_date End_date
   0001   001   2010-06-05 2010-06-07
   0001   002   2010-06-01 2010-06-28


max(recvd_date) = 2010-06-05 in row_seq = 1
max(End_date) = 2010-06-28 in row_seq = 2

If I can read the SQL correctly, you won't receive any row for partkey = 0001
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 29, 2010 5:56 pm
Reply with quote

since seqnum is the only thing changing, why do you not have it in your sql to qualify partynum?
Back to top
View user's profile Send private message
JnanaR

New User


Joined: 20 Jul 2009
Posts: 23
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:59 pm
Reply with quote

You are right GuyC.I put it in the wrong way.
Actually first I have to fetch end date for a particular party key where the recvd date is max.If the subquery for recvd date gives
-811 then i am fetching the row where end date is max.
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 RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts SELECT from data change table DB2 5
Search our Forums:

Back to Top