Hi
I have query to return least date across couple of date fields.. nulls should be ignored during computation. I used the LEAST function with NVL but not able to resolve the issue untill I used the REPLACE function on top of it to show NULL if query return NVL date. Please find the tables and queries that I used..
CREATE TABLE TEST_LEAST
(RECORD NUMBER,
DATE1 DATE,
DATE2 DATE,
DATE3 DATE);
INSERT INTO TEST_LEAST VALUES (1, '1 AUG 2009', '23 JUN 2009', '4 APR 2009');
INSERT INTO TEST_LEAST VALUES (2, '20 JAN 2009', '16 FEB 2009', '7 MAY 2009');
INSERT INTO TEST_LEAST VALUES (3, NULL, '31 MAR 2009', '19 JUL 2009');
INSERT INTO TEST_LEAST VALUES (4, NULL, NULL, NULL);
COMMIT;
To return the least date across date1, date2 and date3, I used the LEAST function but the result were not as expected with the following query:
SELECT RECORD, LEAST(DATE1, DATE2, DATE3) FROM TEST_LEAST;
Record 3 should show the least date of 31 MAR 2009.
I modified the query to use NVL to handle the NULLs...
SELECT CASENBR, LEAST(NVL(DATE1, TO_DATE('31 DEC 9999')), NVL(DATE2, TO_DATE('31 DEC 9999')), NVL(DATE3, TO_DATE('31 DEC 9999'))) FROM TEST_LEAST;
..but now the result shows Record 4 is 31 DEC 2009 instead of NULL.
and finally I replaced the date '31 DEC 2009' with NULL with this query...
SELECT CASENBR, REPLACE(LEAST(NVL(DATE1, TO_DATE('31 DEC 9999')), NVL(DATE2, TO_DATE('31 DEC 9999')), NVL(DATE3, TO_DATE('31 DEC 9999'))), TO_DATE('31 DEC 9999'), NULL) FROM TEST_LEAST;
I am not convinced with query so thought of asking THE GURUS for help. Please guide me how can I best handle the NULLs with LEAST function.
Thanks in advance!