Oracle CASE statement precompile error in Micro Focus

We are converting our COBOL from DB2 to Oracle (also IBM z/OS to Micro
Focus Server Express on Linux) but have run into an Oracle precompile
issue:  Two cursors that use the CASE statement in DB2.  While I
believe the CASE statement is not ANSI SQL, Oracle's documentation
suggests it also supports (since 8i) the CASE statement in what appears
to be the nearly the same format as DB2.

To test that the error was due to the precompiler (and not the large
complex cursor), I created a small, simple cursor:

EXEC SQL
DECLARE TEST_CURS CURSOR WITH HOLD FOR
SELECT 'TESTING'
, CASE
WHEN (7 > 5)
THEN 'TRUE'
ELSE 'FALSE'
END AS TEST_VAL
FROM DUAL
END-EXEC.

However, when I precompile the COBOL program in Server Express with
Oracle 10g, I get the following error suggesting an issue with the
"WHEN" clause of the CASE statement:


606             EXEC SQL
607               DECLARE TEST_CURS CURSOR WITH HOLD FOR
608                 SELECT 'TESTING'
609                      , CASE
Error at line 610, column 30 in file testprog.sks
610                          WHEN (7 > 5)
610  .............................1
610  PCB-S-00400, Encountered the symbol "(" when expecting one of
the following:

, INTO FROM

611                            THEN 'TRUE'
612                          ELSE 'FALSE'
613                        END AS TEST_VAL
^LPro*COBOL: Release 10.1.0.2.0 - Production on Tue Jul 26 16:29:16
2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

614                   FROM DUAL
615             END-EXEC.



I also tried it without the enclosing parentheses, but with no luck.
The error simply points to the "7" instead.

I appreciate any help someone could provide on the topic and thanks in
advance.

Thanks,
Craig

The error is indicating that it has interpreted "CASE" as a column name - it
is therefore expecting what it is saying
a "," for the next column, a "FROM"  to indicate the source or an "INTO"
where to put it (which doesn't make sense to me on the cursor declaration
but there goes).  I would assume that this is not supported (at least with
the level of the precompiler that you have) though I _don't_ know this.

Why not just fetch "thing" and then in the code do the logic

IF "value='compare value'" THEN value = "other value".

I cannot think of many examples where this would be very difficult unless
your case statements are in temp tables you are
materializing as part of a subquery and so forth.  If it is easy, I would do
it this way - depending on the circumstances, some may find it easier to
follow code logic versus sql logic.  In my experience people don't take an
awful long time looking at the intricacies of the SQL before looking at the
"real" code.


Not a lot of help, but always point out the obvious first :-)

JCE

"Craig" <craig.essington@gmail.com> wrote in message
news:1122413976.136702.126550@f14g2000cwb.googlegroups.com...
> We are converting our COBOL from DB2 to Oracle (also IBM z/OS to Micro
> Focus Server Express on Linux) but have run into an Oracle precompile
> issue:  Two cursors that use the CASE statement in DB2.  While I
> believe the CASE statement is not ANSI SQL, Oracle's documentation
> suggests it also supports (since 8i) the CASE statement in what appears
> to be the nearly the same format as DB2.
>
> To test that the error was due to the precompiler (and not the large
> complex cursor), I created a small, simple cursor:
>
>    EXEC SQL
>      DECLARE TEST_CURS CURSOR WITH HOLD FOR
>        SELECT 'TESTING'
>             , CASE
>                 WHEN (7 > 5)
>                   THEN 'TRUE'
>                 ELSE 'FALSE'
>               END AS TEST_VAL
>          FROM DUAL
>    END-EXEC.
>
> However, when I precompile the COBOL program in Server Express with
> Oracle 10g, I get the following error suggesting an issue with the
> "WHEN" clause of the CASE statement:
>
>
>   606             EXEC SQL
>   607               DECLARE TEST_CURS CURSOR WITH HOLD FOR
>   608                 SELECT 'TESTING'
>   609                      , CASE
> Error at line 610, column 30 in file testprog.sks
>   610                          WHEN (7 > 5)
>   610  .............................1
>   610  PCB-S-00400, Encountered the symbol "(" when expecting one of
> the following:
>
>           , INTO FROM
>
>   611                            THEN 'TRUE'
>   612                          ELSE 'FALSE'
>   613                        END AS TEST_VAL
> ^LPro*COBOL: Release 10.1.0.2.0 - Production on Tue Jul 26 16:29:16
> 2005
>
> Copyright (c) 1982, 2004, Oracle.  All rights reserved.
>
>   614                   FROM DUAL
>   615             END-EXEC.
>
>
>
> I also tried it without the enclosing parentheses, but with no luck.
> The error simply points to the "7" instead.
>
> I appreciate any help someone could provide on the topic and thanks in
> advance.
>
> Thanks,
> Craig
>

JCE -

Thanks for the quick reply.

Unfortunately this was just a simple example for posting.  The actual
CASE statement is buried in a subquery of a more complex query.  Having
the CASE statement there meant I only needed one cursor where the
alternative is two cursors with the additional OPEN, FETCH and CLOSE
statements.

On a side note, Oracle manuals discuss CASE alongside DECODE.  It
appears that DECODE can do all the things a CASE statement can but with
more complexity and less readability than the CASE statement.  I
converted the CASE to a DECODE and the compile worked.  Now its a
matter of testing the change, but I would still prefer the CASE...