I think that the last line is the problem and that you have to change it to
AND D1.EFFDT <= TO_DATE('01-JAN-2002', 'DD-MON-YYYY'));
I think that he is converting D1.EFFDT to char-format instead of converting
'01-JAN-2002' to date-format.
Best regards,
Marco
-----Oorspronkelijk bericht-----
Van: Townsend Willie [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 21 januari 2002 16:15
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Oracle select data problem...
Oracle Gurus,
I have a perplexing problem....I recently installed Oracle 8.1.7.0 (64 bit)
on an HP 11.X (64 bit) machine running a HRMS/PAyroll type application. I
am having a problem with the below sql statement not returning any rows.
When I look at the data, a row should be returned. Any ideas or solutions
to this problem? Also, when I execute the subselect as a standalone SQL, a
row of data is returned.
SELECT SETID,
EFFDT
FROM PSTREEDEFN D
WHERE D.EFF_STATUS = 'A'
AND D.TREE_NAME = 'DEPT_SECURITY'
AND D.EFFDT = (SELECT MAX(D1.EFFDT)
FROM PSTREEDEFN D1
WHERE D1.SETID = D.SETID
AND D1.TREE_NAME = D.TREE_NAME
AND D1.EFFDT <= '01-JAN-2002');
Willie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Townsend Willie
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rooy van, Marco
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
