And the max function works without being in the subselect?

Try 'in' instead of '=' against the subselect.

The engine may not know the subselect will only get one value.

JDPope

-----Original Message-----
From: Goethel Rob
[mailto:db2udbdba-ezmlmshield-x33845223.[Email address protected]
Sent: Friday, February 23, 2007 9:51 AM
To: LazyDBA Discussion
Subject: RE: Max function and join DB2 Z/OS V7

Yes, without the max function, I receive the data. It should be getting
the max referral_timestamp from the d001.adt044_ref_act table. I added
c.referral_timestamp = (select max...) to make sure it was looking at
the correct table's column, but that is unique anyway. No luck yet.

Thanks


Robert Goethel
DBA

-----Original Message-----
From: Pope John
[mailto:db2udbdba-ezmlmshield-x39429355.[Email address protected]
Sent: Friday, February 23, 2007 8:12 AM
To: LazyDBA Discussion
Subject: RE: Max function and join DB2 Z/OS V7

Quick pass.....

Could be confused as to which table to match the 'REFERRAL_TIMESTAMP'
with? I would think you'd get an error about that though.

Have you looked at the data - is there data that will match the
conditions in the where clause?

Break down the where clause and see where you stop getting data.

JDPope

-----Original Message-----
From: Goethel Rob
[mailto:db2udbdba-ezmlmshield-x35099275.[Email address protected]
Sent: Friday, February 23, 2007 8:35 AM
To: LazyDBA Discussion
Subject: Max function and join DB2 Z/OS V7

When I query just the main table with the max function, it works fine.
Combined in the join, it returns zero rows. Any suggestions?

SELECT N_DOC,
N_POL,
I_CLAIMANT_ID,
D_DOC_RECEIVED,
D_PEND_ORIGINAL,
I_ORIG_PEND_EMP,
C_ORIG_PEND_UNIT,
D_PEND_TO,
C_PEND_REASON,
CLAIM_STATUS,
PROVIDER_STATE,
UNIT_REFERRED_TO
FROM D001.ADT018_COB_DOC A, D001.ADT043_REF_CLM_HD B,
D001.ADT044_REF_ACT C
WHERE SUBSTR(N_POL,1,1) LIKE '%T%'
AND C_PEND_REASON LIKE '%PHC%'
AND I_ORIG_PEND_EMP LIKE '%%%%%%%%%%%%'
AND C_ORIG_PEND_UNIT LIKE '%CRPU%'
AND N_DOC = B.DOCUMENT
AND N_DOC = C.DOCUMENT
AND PROVIDER_STATE LIKE '%WI%'
AND CLAIM_STATUS LIKE '%OPN%'
AND UNIT_REFERRED_TO LIKE '%VMUP%';
AND REFERRAL_TIMESTAMP = (SELECT MAX(REFERRAL_TIMESTAMP)
FROM D001.ADT044_REF_ACT);

Robert Goethel
DBA




************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.

Thank you.
************************************************************************
**************


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.

Thank you.
************************************************************************
**************


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page