1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using CASE statement in the WHERE clause

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Mar 3, 2011.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello To All,

    I need to use the CASE statement in the WHERE clause like:

    AND cctl_paymentmethod.typecode =
    CASE WHEN 'manual' THEN cc_check.createtime BETWEEN (TRUNC(sysdate)-2+14/24) AND (TRUNC(sysdate)-1+14/24)
    ELSE (cc_check.createtime BETWEEN (TRUNC(sysdate)-2+18/24) AND (TRUNC(sysdate)-1+18/24))
    END


    I will get the "ORA-00905: missing keyword" error. How can I fix this?


    Respectfully yours,
    Farshad
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Do you mean to say ,

    Code (SQL):

    AND cctl_paymentmethod.typecode =
    CASE WHEN cc_check.createtime BETWEEN (TRUNC(sysdate)-2+14/24) AND (TRUNC(sysdate)-1+14/24)  THEN 'manual'
    ELSE  NULL
    END
     
    Else what were you trying to do? Anyway please have a look at CASE for more details.
     
  3. neullson

    neullson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Can you give us more detail..?? I think you have syntax problem. I think, you have to learn more about CASE operation... :)