Home » SQL & PL/SQL » SQL & PL/SQL » Case in sql statement doesn't run in PLSql block
icon1.gif  Case in sql statement doesn't run in PLSql block [message #119940] Mon, 16 May 2005 16:52 Go to next message
justvishnu
Messages: 2
Registered: May 2005
Junior Member
SELECT col1, col2,
CASE
WHEN col3 < 200 THEN
170
ELSE col4 (type number)
END AS col4,
col5, col6, col7, col8,
INTO o_col1, o_col2, o_col4,
o_col5, o_col6, o_col7, o_col8
FROM table
WHERE col1= i_col1 AND col2 = i_col2;

The above query runs fine without the into statement in the sql plus window. But fails in the plsql window with a compilation error Can any one help me out.

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

( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current

Thanks

Vishnu
Re: Case in sql statement doesn't run in PLSql block [message #119949 is a reply to message #119940] Mon, 16 May 2005 18:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
It helps if you tell us what verson of Oracle you are using. In older versions, CASE was only available in SQL, not PL/SQL, so you would either have to use DECODE and SIGN or dynamic SQL. Please see the untested revision using DECODE and SIGN below.

SELECT col1, col2,
       DECODE (SIGN (col3 - 200), -1, 170, col4) AS col4,
       col5, col6, col7, col8,
INTO   o_col1, o_col2, o_col4,
       o_col5, o_col6, o_col7, o_col8
FROM   table
WHERE  col1= i_col1 AND col2 = i_col2;

icon14.gif  Re: Case in sql statement doesn't run in PLSql block [message #119950 is a reply to message #119949] Mon, 16 May 2005 18:53 Go to previous messageGo to next message
justvishnu
Messages: 2
Registered: May 2005
Junior Member
Thanks very much pal for taking time out and coming to my aid. I am using oracle 8i and after further research came to know that PLSQL in 8i didn't support case. So I solved it using the decode statement almost in the similar fashion. This is what I did.

select decode(trunc(col1/200),0,170,col2) from table
SELECT col1, col2,
DECODE (trunc(col3/200),0, 170, col4) AS col4,
col5, col6, col7, col8,
INTO o_col1, o_col2, o_col4,
o_col5, o_col6, o_col7, o_col8
FROM table
WHERE col1= i_col1 AND col2 = i_col2;

anyway thanks again for helping me out.

Case in sql statement doesn't run in Forms6i [message #447808 is a reply to message #119950] Thu, 18 March 2010 01:49 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
cursor cemp is
SELECT P.EMPID,ROUND(FACTOR*BASIC_AMOUNT) AS AMOUNT,ENTRY_DATE,V.GRADE_ID,
CASE WHEN (V.GRADE_ID BETWEEN 1 AND 5) THEN 'a'
wHEN (V.GRADE_ID BETWEEN 6 AND 15) THEN 'b'
WHEN (V.GRADE_ID BETWEEN 16 AND 22) THEN 'c'
ELSE 'z' END V_USER_char
FROM PAYMENT_RECORD_DETAIL P
JOIN VW_EMP V ON P.EMPID = V.EMPID
wHERE EMP_PAY_ID = 5
AND PAY_YEAR = 2009 ;

Hi,
The above cursor written in a procedure on form is giving following compilation error.
" Error 103 at line 8, column 20
Encountered the symbol CASE when expecting one of the
following:"

But it works fine on sql plus. I am using Oracle database 9i with Forms6i(Forms [32 Bit] Version 6.0.8.11.3 (Production)).
Kindly suggest a solution

Re: Case in sql statement doesn't run in Forms6i [message #447812 is a reply to message #447808] Thu, 18 March 2010 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is line 8?

I bet this syntax is not know from your aged version. Upgrade to a newer one.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Case in sql statement doesn't run in Forms6i [message #447844 is a reply to message #447808] Thu, 18 March 2010 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
babar82
I am using Oracle database 9i with Forms6i

It appears that Forms 6i's PL/SQL engine doesn't support CASE. Which, probably, means that you'll need to rewrite it using DECODE.
Re: Case in sql statement doesn't run in Forms6i [message #447862 is a reply to message #447812] Thu, 18 March 2010 04:51 Go to previous messageGo to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
Does Forms6i PL/SQL engine does not support CASE statement. Any other version of FORMS6i which supports CASE statement??
Re: Case in sql statement doesn't run in Forms6i [message #447864 is a reply to message #447862] Thu, 18 March 2010 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Does Forms6i PL/SQL engine does not support CASE statement

That should be easy for you to check - write a piece of pl/sql that uses CASE, and try in in FORMS and on the server.

 Any other version of FORMS6i which supports CASE statement??

No - the Pl/sql engine didn't get patched during it's lifetime as far as I know. Forms 9 supports case.
Re: Case in sql statement doesn't run in Forms6i [message #447865 is a reply to message #447862] Thu, 18 March 2010 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No other version of FORMS6i but next versions of FORMS like Forms 10g.

Regards
Michel
Re: Case in sql statement doesn't run in Forms6i [message #447871 is a reply to message #447808] Thu, 18 March 2010 05:04 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
1) Upgrade to the latest release.
2) Write a method at the database level and call it from the forms.

3) Quote:
It appears that Forms 6i's PL/SQL engine doesn't support CASE. Which, probably, means that you'll need to rewrite it using DECODE.


sriram Smile
Previous Topic: Compilation error with simple ELSIF
Next Topic: how to use analytic functions in this case
Goto Forum:
  


Current Time: Fri Sep 30 14:04:15 CDT 2016

Total time taken to generate the page: 0.15436 seconds