Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: Problems with UPDATE and CASE in the same Sentence !

    Hi there ...

    I have one problem with one Package of mine, and I would appreciate any help from you on this matter.
    At one Package of mine, I have the following sentence:
    UPDATE table1 SET table1.field1 =
    CASE WHEN (variableX = 1) THEN 0
    ELSE table1.field1
    END
    ,table1.field2 =
    CASE WHEN (variableX = 1) THEN 0
    ELSE table1.field1
    END
    WHERE table1.COD_field3 = variableY

    When I try to compile it it gives me the error:
    ***********
    00103: Encountered the symbol "CASE" when expecting one of the following:
    ( - + mod null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current max mi
    ***********

    But when I run this SQL at SQL Plus (or with TOAD), for instance, it gives me no Error, and works fine ...

    I am working with Oracle8i Release 8.1.7.4.1
    Does anyone know what it might happen ??

    Thanx !!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    CASE statement is not allowed within PL/SQL procedure.
    Replace by DECODE.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    Thanks !

    That seemed to work just fine ...

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up

    Good job!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    This restriction is lifted in 9i.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Yes, but he's using 8.1.7
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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