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
cutemochachickAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
use case instead of decode.  decode can only do equality not greater than or less than

case
when dateA > dateB then
    max(dateA) - max(dateB)
else
 0
end
0
 
sonicefuCommented:
<< Title:  SQL PLUS Decode Statement  >>
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;
0
 
sonicefuCommented:
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;
0
 
Jinesh KamdarCommented:
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 ...

Open in new window

0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
All Courses

From novice to tech pro — start learning today.