0

I have simply written CASE statement in the procedure as below

(case when (T1.DEVICEHOLD = 'Z1' or T2.ISHOLD = 1) then 1
       else 0
       END) AS HOLD

But when I'm compling procedure its giving an error Compilation errors for PACKAGE BODY 

Error: PLS-00103: Encountered the symbol "Z1" when expecting one of the following:

          * & = - + ;  at in is mod remainder not rem
            or != or ~= >=  and or like LIKE2_
          LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
       The symbol "* was inserted before "Z1" to continue.

Could anyone help me to resolve this?

Thanks in advance!

user272735
  • 10,473
  • 9
  • 65
  • 96
cool_taps
  • 340
  • 1
  • 4
  • 16
  • Try removing all parenthesis, it is not needed here. Post more code where your case is used so we have the context. – Yaroslav Shabalin Dec 26 '13 at 11:16
  • 1
    The code posted will not cause this error, therefore you haven't posted exactly what is causing the error. Can you please post the cause of the error? Are your quotes exactly the same ones you have in your code? – Ben Dec 26 '13 at 11:17
  • Can you post the query block with the above statement? – Incognito Dec 26 '13 at 11:19
  • @Ben--I have psoted exact. I haqve written 'Z1' only but dont know why it is giving such an error – cool_taps Dec 26 '13 at 11:20
  • First of all, if you're using the CASE statement inside a PL/SQL block (and not within a SQL query), then you need to end the case with `END CASE`. And the output of the CASE evaluation should be assigned to a variable. – Incognito Dec 26 '13 at 11:24

1 Answers1

3

This is a compiler error. The most likely cause is a stray ' somewhere which means that the first ' in your snippet terminates a quoted string and so the compiler considers Z1 to be code, and not valid as such.

The easiest way to spot such things is to use a decent editor or IDE which has syntax highlighting. These tools colourize quotes, keywords and comments and so make it easy to spot where we have made our bloomers. There are plenty of free tools available: Notepad++ or Oracle SQL Developer are popular options.

APC
  • 144,005
  • 19
  • 170
  • 281