Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Case St in where clause

    Hi All,

    I have some problem using the case statement in the where clause.

    I have to retrieve data from table based on some conditions ,
    in the where clause in have used Case , is this format correct

    I have given a sample code below

    select ca.ca_id_contr_attest from cdl_tr_contr_attest ca
    where CA.CA_ULTIMA_DATA_ATTEST = SYSDATE OR
    CASE
    WHEN To_Char(Trunc(SYSDATE) , 'D' )=7 AND CO.CO_TYPE IN ( 10011, 10011 )
    THEN CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+1)
    ELSE
    CASE WHEN (To_Char(Trunc(SYSDATE) , 'D' ) = 6 )
    THEN CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+1) OR CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+2) AND CO.CO_TYPE IN ( 10011, 10011 ) )
    END
    END
    END

    CASE
    WHEN (To_Char(Trunc(SYSDATE) , 'D' ) = 5
    THEN ( ( CA.CA_ULTIMA_DATA_ATTEST >= Trunc(SYSDATE+1) AND CA.CA_ULTIMA_DATA_ATTEST <= Trunc(SYSDATE+3) AND CO.CO_TYPE = 10011 )
    OR ( CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+1) AND CO.CO_TYPE = 10011 )
    OR ( 2 > 1
    AND ( ( CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+2) OR CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+3)) AND CO.CO_TYPE = 10011 ) ) )
    WHEN (To_Char(Trunc(SYSDATE) , 'D' ) = 6 )
    THEN ( (CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+1) OR CA.CA_ULTIMA_DATA_ATTEST = Trunc(SYSDATE+2)) AND CO.CO_TYPE = 10011 )


    // check for monday , tuesday and wednesday pre holiday
    WHEN (To_Char(Trunc(SYSDATE) , 'D' ) != 7
    THEN (CA.CA_ULTIMA_DATA_ATTEST > Trunc(SYSDATE) AND CA.CA_ULTIMA_DATA_ATTEST <= Trunc(SYSDATE + 10011 )

    CASE WHEN ( (To_Char(Trunc(SYSDATE + 10011 ) , 'D' ) = 6
    THEN (CA.CA_ULTIMA_DATA_ATTEST > Trunc(SYSDATE) AND CA.CA_ULTIMA_DATA_ATTEST <= Trunc(SYSDATE + 10011 ) )
    WHEN ( ( To_Char(Trunc(SYSDATE + 10011 ) ,'D' ) = 7
    THEN (CA.CA_ULTIMA_DATA_ATTEST > Trunc(SYSDATE) AND CA.CA_ULTIMA_DATA_ATTEST <= Trunc(SYSDATE + 10011 ) )
    END
    END

    Thanks
    Preetha

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, CASE is used to generate a value not a predicate. Use OR:

    Code:
    select ca.ca_id_contr_attest from cdl_tr_contr_attest ca
    where CA.CA_ULTIMA_DATA_ATTEST = SYSDATE 
    OR (To_Char(Trunc(SYSDATE) , 'D' )=7 AND CO.CO_TYPE IN ( 10011, 10011 ))
    OR ...

Posting Permissions

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