cutemochachick
asked on
SQL PLUS Decode Statement
I am creating a view in SQL PLUS. The view is using many table. I want to use a create a calculated field based 2 dates fields (one of which is displayed in the view and one that is not).
Right now my calculated field finds the days in between 2 days
max(dateA) - max(dateB) as MyDate
I need to change it to this logic
if(dateA) > (dateB) then
max(dateA) - max(dateB)
else
0
I tried using a decode statement but not familiar with complex statement.
Thanks in advance for your help
Right now my calculated field finds the days in between 2 days
max(dateA) - max(dateB) as MyDate
I need to change it to this logic
if(dateA) > (dateB) then
max(dateA) - max(dateB)
else
0
I tried using a decode statement but not familiar with complex statement.
Thanks in advance for your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE VIEW testview
AS
SELECT col1, col2,
(SELECT (CASE
WHEN (datea > dateb)
THEN MAX (datea) - MAX (dateb)
ELSE 0
END
) mydate
FROM tablename a
WHERE a.col1 = b.col1) mydat
FROM tablename b;
AS
SELECT col1, col2,
(SELECT (CASE
WHEN (datea > dateb)
THEN MAX (datea) - MAX (dateb)
ELSE 0
END
) mydate
FROM tablename a
WHERE a.col1 = b.col1) mydat
FROM tablename b;
As others have mentioned, the usage of DECODE does not fit ur requirement. You can use CASE instead.
SELECT (CASE WHEN MAX(dateA) > MAX(dateB) THEN MAX(dateA) - MAX(dateB) ELSE MAX(dateB) - MAX(dateA) END) AS MyDate, ...
FROM ...
WHERE ...
GROUP BY ...
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
cutemochachick ! decode is not a SQL*Plus statement, decode is an SQL function
--------------
CREATE VIEW testview
AS
SELECT (CASE
WHEN (datea > dateb)
THEN MAX (datea) - MAX (dateb)
ELSE 0
END) mydate
FROM tablename;