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
 

 

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: 6967
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: 6967
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: 6967
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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