Oracle Case Statement
Hi All,
I need your help. I have the query below but I do not think is working as expected.
The business rule is that Date1 must be the earliest date, if not then show all records where Date1 is not the earliest. I started with this query but I think it needs some twitching to work, could you please help.
SELECT f.id_number,f.status,E.Dat
CASE
WHEN E.Date1 > E.Date2 THEN 'Date2' || ' ' || E.Date2
WHEN E.Date1 < E.Date3 THEN 'Date3' || ' ' || E.Date3
WHEN E.Date1 < E.Date4 THEN 'Date4' || ' ' || E.Date4
WHEN E.Date1 < E.Date5 THEN 'Date5' || ' ' || E.Date5
WHEN E.Date1 < E.Date6 THEN 'Date6' || ' ' || E.Date6
END AS THE_DATE
FROM
TableE E
LEFT JOIN TableF F
ON F.id_number = E.id_number
WHERE E.Date1 IS NOT NULL
I need your help. I have the query below but I do not think is working as expected.
The business rule is that Date1 must be the earliest date, if not then show all records where Date1 is not the earliest. I started with this query but I think it needs some twitching to work, could you please help.
SELECT f.id_number,f.status,E.Dat
CASE
WHEN E.Date1 > E.Date2 THEN 'Date2' || ' ' || E.Date2
WHEN E.Date1 < E.Date3 THEN 'Date3' || ' ' || E.Date3
WHEN E.Date1 < E.Date4 THEN 'Date4' || ' ' || E.Date4
WHEN E.Date1 < E.Date5 THEN 'Date5' || ' ' || E.Date5
WHEN E.Date1 < E.Date6 THEN 'Date6' || ' ' || E.Date6
END AS THE_DATE
FROM
TableE E
LEFT JOIN TableF F
ON F.id_number = E.id_number
WHERE E.Date1 IS NOT NULL
Pawan Kumar
Could you pls provide some sample data and the expected output ?
we expect questions are asked in this format :)
maybe this
this is meaningful if Date2<Date3<Date4<Date5<Da
CASE
WHEN E.Date1 < E.Date2 THEN 'Date2 ' || E.Date2
WHEN E.Date1 < E.Date3 THEN 'Date3 ' || E.Date3
WHEN E.Date1 < E.Date4 THEN 'Date4 ' || E.Date4
WHEN E.Date1 < E.Date5 THEN 'Date5 ' || E.Date5
WHEN E.Date1 < E.Date6 THEN 'Date6 ' || E.Date6
ELSE '???'
END AS THE_DATEthis is meaningful if Date2<Date3<Date4<Date5<Da
let it be clear,
i don't expect questions in excel format
you really don't need excel when you have an oracle database below the hood.
to find the earliest date, use the LEAST function
you don't have a criteria in the where clause which fits your question
i don't expect questions in excel format
you really don't need excel when you have an oracle database below the hood.
to find the earliest date, use the LEAST function
you don't have a criteria in the where clause which fits your question
SELECT f.id_number,f.status,E.Date1,E.Date2,E.Date3,E.Date4,E.Date5,E.Date6
FROM
TableE E
LEFT JOIN TableF F
ON F.id_number = E.id_number
WHERE E.Date1 IS NOT NULL
and E.Date1 > LEAST(E.Date2, E.Date3, E.Date4, E.Date5, E.Date6)
I would expect the where clause to be restrictive:
where e.Date1 > e.Date2
or e.Date1 > e.Date3
or e.Date1 > e.Date4
or e.Date1 > e.Date5
or e.Date1 > e.Date6ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.