Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Compute Case Statement ; DB Version 8i

    I wish to compute my statement as below:

    I have three date in my entire SQL statement and I wish to achieve condition as below:

    If Start_Date <> Null Then Start_Date
    Else if End_Date <> Null Then End_Date + et_days (Is an extension days)
    Else if Revised_Date <> Null Then Revised_Date


    From the three dates, only one date is return as "Final_Date" and it is less_date Sysdate. My SQL statement is as below:

    select p.end_date,(p.start_date + p.et_days) as mydate,

    (CASE
    WHEN p.start_date is not null THEN p.start_date
    WHEN p.end_date is not null THEN (p.end_date + p.et_days)

    WHEN p.revised_date is not null THEN p.revised



    END ) finaldate

    from MY_REGISTRATION p ,

    where

    (CASE
    WHEN p.start_date is not null THEN p.start_date
    WHEN p.end_date is not null THEN (p.end_date + p.et_days)

    WHEN p.revised_date is not null THEN p.revised



    END ) finaldate > sysdate;


    Once execute, I have problem in :

    ORA-00932: inconsistent datatypes - get this message if I add the p.et_days

    ORA-00920: invalid relational operator - get this error mesage if I add the case statement after where clause

    pls advise.

    Ying

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What are the data types of the columns involved? A similar (contrived) CASE works for me, but I am on 10G so that may not mean much:
    Code:
    select case when job='MANAGER' then hiredate
                when job='CLERK' then hiredate+empno
                else hiredate+deptno
           end
    from   emp
    Incidentally, if you can assume et_days is always present when end_date is present, then this is equivalent:
    Code:
    COALESE (p.start_date, (p.end_date + p.et_days), p.revised_date) finaldate
    Your WHERE clause problem is a matter of invalid syntax (once the CASE data type problem is solved) - remove the "finaldate" column alias:
    Code:
    where
    (CASE
    WHEN p.start_date is not null THEN p.start_date
    WHEN p.end_date is not null THEN (p.end_date + p.et_days)
    WHEN p.revised_date is not null THEN p.revised
    END ) > sysdate;
    Or if you prefer:
    Code:
    select mydate, finaldate
    from
    ( select p.end_date,(p.start_date + p.et_days) as mydate,
             (CASE WHEN p.start_date is not null THEN p.start_date
                   WHEN p.end_date is not null THEN (p.end_date + p.et_days)
                   WHEN p.revised_date is not null THEN p.revised
                   END ) finaldate
      from MY_REGISTRATION p
    )
    where finaldate > sysdate;

  3. #3
    Join Date
    Sep 2006
    Posts
    4

    Try to use Decode

    Is the same, I ecounter ORA-00932: inconsistent datatypes, this is due to (p.end_date + p.et_days). I tried to use Decode, but still not manage to compute the statement. Anyhelp is much appreciated.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by yingying
    Is the same, I ecounter ORA-00932: inconsistent datatypes, this is due to (p.end_date + p.et_days).
    What is the datatype of the et_days column?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •