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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.