Procedure variable scope?

[ShaggyMoose]

I am using DB2 on AS400. I am trying to use a variable in a CREATE
TABLE AS statement. However, I am informed that the variable is not a
column of the FROM table... It feels like a scope issue in the SELECT,
but I don't see how. Please help, I hate getting frustrated by
something so basic.

In the procedure below, the first statement compiles fine. The second
does not, with the error "Position n Column MYVAR not in table
MYTABLE".

CREATE PROCEDURE MYPROC(IN MYVAR CHAR(8))
LANGUAGE SQL

BEGIN

INSERT INTO TEST1
SELECT
FIELDA
FROM MYTABLE
WHERE FIELDA = MYVAR;

CREATE TABLE TEST2 AS (
SELECT
FIELDA
FROM MYTABLE
WHERE FIELDA = MYVAR
) WITH DATA;

END

[Serge Rielau]

ShaggyMoose wrote:

I am using DB2 on AS400. I am trying to use a variable in a CREATE
TABLE AS statement. However, I am informed that the variable is not a
column of the FROM table... It feels like a scope issue in the SELECT,
but I don't see how. Please help, I hate getting frustrated by
something so basic.

In the procedure below, the first statement compiles fine. The second
does not, with the error "Position n Column MYVAR not in table
MYTABLE".

CREATE PROCEDURE MYPROC(IN MYVAR CHAR(8))
LANGUAGE SQL

BEGIN

INSERT INTO TEST1
SELECT
FIELDA
FROM MYTABLE
WHERE FIELDA = MYVAR;

CREATE TABLE TEST2 AS (
SELECT
FIELDA
FROM MYTABLE
WHERE FIELDA = MYVAR
) WITH DATA;

END

DB2 does not allow the usage of variables in DDL statements.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab