Try this:
SELECT DEPTNUM.REF_VALUE
,CAMNUM.REF_VALUE
,PN1.NOTE_TEXT
FROM PARTY_REFERENCE DEPTNUM
, PARTY_REFERENCE CAMNUM
, PARTY_ASSOC REL
, PARTY_NOTE PN1
WHERE DEPTNUM.PARTY_ID = REL.TO_PARTY_ID
AND REL.FROM_PARTY_ID = CAMNUM.PARTY_ID
AND DEPTNUM.PARTY_REF_TYPE_CD = 'TYPE1'
AND CAMNUM.PARTY_REF_TYPE_CD = 'TYPE2'
AND PN1.PARTY_ID = DEPTNUM.PARTY_ID
AND PN1.last_update_dt = (select max(pn2.last_update_dt) from
PARTY_NOTE pn2 WHERE PN1.PARTY_ID = pn2.PARTY_ID)
-----Original Message-----
From: ALEX HYDE [mailto:[EMAIL PROTECTED]
Sent: Friday, February 03, 2006 10:25 AM
To: Struts Users Mailing List
Subject: Re: For sql experts
FROM PARTY_NOTE PN 1WHERE PN.1PARTY_ID
might have to be...
FROM PARTY_NOTE PN1 WHERE PN1.PARTY_ID
What happens if you get more than one last_update_dt equal to the
maximum?
Gluck
"Jonnalagadda, Sumithra" <[EMAIL PROTECTED]> wrote:
SELECT DEPTNUM.REF_VALUE, CAMNUM.REF_VALUE,
(SELECT PN1.NOTE_TEXT FROM PARTY_NOTE PN 1WHERE PN.1PARTY_ID =
DEPTNUM.PARTY_ID and max(pn1.last_update_dt)) NOTE
FROM PARTY_REFERENCE DEPTNUM,
PARTY_REFERENCE CAMNUM,
PARTY_ASSOC REL
WHERE DEPTNUM.PARTY_ID = REL.TO_PARTY_ID
AND REL.FROM_PARTY_ID = CAMNUM.PARTY_ID
AND DEPTNUM.PARTY_REF_TYPE_CD = 'TYPE1
AND CAMNUM.PARTY_REF_TYPE_CD = 'TYPE2'
The problem is the the sub query
SELECT PN1.NOTE_TEXT FROM PARTY_NOTE PN 1WHERE PN.1PARTY_ID =
DEPTNUM.PARTY_ID and max(pn1.last_update_dt
is not working.
My requirement is in the sub query I want to select those notes with
last_update_dt is maximun for each party.
Any help is appriciated.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------
To help you stay safe and secure online, we've developed the all new
Yahoo! Security Centre.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
