Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
APPROPRIATE SELECT QUERY

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

New User


Joined: 20 Jul 2009
Posts: 24
Location: Mumbai

PostPosted: Tue Jun 29, 2010 4:52 pm    Post subject: APPROPRIATE SELECT QUERY
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: 6970
Location: porcelain throne

PostPosted: Tue Jun 29, 2010 5:05 pm    Post subject:
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: 24
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:12 pm    Post subject: Reply to: APPROPRIATE SELECT QUERY
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: 6970
Location: porcelain throne

PostPosted: Tue Jun 29, 2010 5:26 pm    Post subject:
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: 24
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:40 pm    Post subject:
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    Post subject:
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: 6970
Location: porcelain throne

PostPosted: Tue Jun 29, 2010 5:56 pm    Post subject:
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: 24
Location: Mumbai

PostPosted: Tue Jun 29, 2010 5:59 pm    Post subject:
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    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 How to force a select query to abend neo4u DB2 4 Mon Apr 23, 2018 1:36 pm
No new posts SELECT TO MULIPLE OUTFIL speermb DFSORT/ICETOOL 5 Fri Mar 23, 2018 10:44 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm
No new posts Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us