Michael Neuber wrote :
>I want to use a stored procedure to invoke some conditional changes in a SAPdb.
>Could the experienced folks and/or SAP specialists please comment on my
>observation,
>that the statement (A) below is well accepted by SQL Studio (Vs 7.4.3),
>but not if I replace the SELECT clause in (A) by the IF EXISTS clause in (B)?
>For the error message: see (C).
>Am I really not allowed to use *parameters* in the EXISTS clause?
>What could be a helpful workaround?
>If I did not recognize where similar questions might have already been answered
>in the archives,
>please excuse me, I tried hard to find an answer for that problem.
>Thanks for your help!
>Kind regards,
>Michael Neuber
>(A)
> CREATE DBPROC testProcedure AS
> VAR ii INTEGER; upperBoundary INTEGER; stack_pointer INTEGER;
> BEGIN
> SET ii = 2;
> SET upperBoundary = 1000;
> SET stack_pointer = 1;
> WHILE (ii < upperBoundary) DO
> BEGIN
> SELECT * FROM OWNER.aTable S1, OWNER.anotherTable T1
> WHERE S1.navid = T1.navid
> AND S1.stack_top = :stack_pointer;
> ...
> ...
>
> SET ii = ii + 1;
> END; /* while */
> END; /*create*/
>
>
>(B)
> IF EXISTS (SELECT * FROM OWNER.aTable S1, OWNER.anotherTable T1
> WHERE S1.navid = T1.navid
> AND S1.stack_top = :stack_pointer)
>
>(C)
>The error message I get on (B) is
>-7045: Parameter spec not allowed
>Explanation: A parameter was specified, although this database component or
>this kind of application programming only allows the use of constants.
>User Action: Replace the parameter with a constant.
The EXISTS predicate in the IF statement is not yet implemented. However, the
parser should return a better error explanation in that case. The
following code shows a workaround :
SELECT COUNT(*) INTO :CNT FROM SYSDBA.DUAL WHERE EXISTS (
SELECT * OWNER.aTable S1, OWNER.anotherTable T1
WHERE S1.navid = T1.navid
AND S1.stack_top = :stack_pointer);
IF CNT > 0
THEN
...
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
