[EMAIL PROTECTED] wrote:
>
> Hello,
>
> i've a simple table like this
>
> CREATE TABLE T_COUNTRIES
> (
> "NAME" Char (3) ASCII UNIQUE,
> "LONGNAME" Varchar (255) ASCII
> )
>
> This table is filled with two records :
> DE - Deutschland
> NL - Niederlande
>
>
> i test the following query in SQL Studio :
>
> SELECT
> '<option value=' || NAME || '>'|| NAME || '</option>' as
> NAME, LONGNAME AS LONGNAME
> FROM
> T_COUNTRIES
> WHERE
> UPPER(NAME) <> UPPER('DE')
> UNION
> SELECT
> '<option value=' || NAME || ' selected>'|| NAME ||
> '</option>'
> as NAME, LONGNAME AS LONGNAME
> FROM
> T_COUNTRIES
> WHERE
> UPPER(NAME) = UPPER('DE')
> ORDER BY
> 1
>
> It runs without any error and returns the result i expected.
>
> But when i use the same statement in a dbproc like
>
> CREATE DBPROC P_TEST
> (
> IN SELITEM VARCHAR(255)
> ) RETURNS CURSOR AS
>
> $CURSOR = 'GETCOUNTRIES';
>
> TRY
> DECLARE :$CURSOR CURSOR FOR
> SELECT
> '<option value=' || NAME ||
> '>'|| NAME ||
> '</option>' as NAME,
> LONGNAME AS LONGNAME
> FROM
> KM_USER.T_COUNTRIES
> WHERE
> UPPER(NAME) <> UPPER(:SELITEM)
> UNION
> SELECT
> '<option value=' || NAME || '
> selected>'|| NAME || '</option>' as NAME,
> LONGNAME AS LONGNAME
> FROM
> KM_USER.T_COUNTRIES
> WHERE
> UPPER(NAME) = UPPER(:SELITEM)
> ORDER BY
> 1;
>
> RETURN;
>
>
> CATCH
>
> IF $RC = 100 THEN CONTINUE EXECUTE;
> STOP ($RC, $ERRMSG);
>
> it will be compiled without any error, but when i execute the dbproc,
> the following error occured :
>
> Native error : -4024
> Text :[SAPAG][SQLOD32.DLL][SAP DB]Generell error;-4024.
>
> without an additional query and UNION, there's no error. It
> doesn't make
> any difference if i use the first or the second query.
> The problem is UNION.
> But i don't know why.
>
> Any idea ?
> Burkhard
Unfortunately there is a bug concerning UNION and resultset-names
given as parameter. (will be corrected)
Use
DECLARE GETCOUNTRIES CURSOR FOR ....
in your dbproc and it will work.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
