View previous topic :: View next topic
|
Author |
Message |
JnanaR
New User
Joined: 20 Jul 2009 Posts: 23 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
JnanaR
New User
Joined: 20 Jul 2009 Posts: 23 Location: Mumbai
|
|
|
|
Primary key is Party Key+Seq Num.
Seq number is not provided. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
JnanaR
New User
Joined: 20 Jul 2009 Posts: 23 Location: Mumbai
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
since seqnum is the only thing changing, why do you not have it in your sql to qualify partynum? |
|
Back to top |
|
|
JnanaR
New User
Joined: 20 Jul 2009 Posts: 23 Location: Mumbai
|
|
|
|
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 |
|
|
|