hi, i wanna do 'select into variable' in dynamic sql.
but i got compile error
plz help

for example:
set str = 'select count(*) into var where..... (var is a variable)
execute immediate str

how do I do that?

Thanks in advance

What programming language (or facility) are you using. What is the datatype for the var (I believe it needs to be int or bigint).

I need to do it in the stored procedure. not from application program

I assume you are using an SQL stored procedure.

Where is your "FROM" clause?

Did you define the variable first?

You can't. "SELECT INTO" is not a dynamic statement.

So, can I actually use select into to produce a table with one row in the stored procedure instead of embedding it in application program?

Thanks

Not as such. However you can use
DECLARE GLOBAL TEMPORARY TABLE mycount (myrowcount integer)
SET str = 'INSERT INTO SESSION.mycount SELECT COUNT(*) FROM ... WHERE ...'
EXECUTE IMMEDIATE str
SELECT myrowcount INTO var FROM SESSION.mycount


James Campbell

I got exception when i try to run this:

DECLARE RC CURSOR WITH RETURN FOR
SELECT * FROM SESSION.TEMP;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(TOT INTEGER) ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TEMP SELECT COUNT(*) FROM IRNI.A_RT_SENTINEL_LOG;

OPEN RC;

Error message:
Exception occurred while running:
"A database manager error occurred.[IBM][CLI Driver][DB2] SQL0873N Objects encoded with different encoding schemes cannot be referenced in the same SQL statement. SQLSTATE=53090"

Please help.
Thanks

As a wild guess, was IRNI.A_RT_SENTINEL_LOG created with CCSID UNICODE while the database was created using some other codeset? Or the other way around. Not a good thing to do - read all the restrictions.

You might have to do something like:
declare myint integer;

DECLARE RC CURSOR WITH RETURN FOR
SELECT * FROM SESSION.TEMP;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(TOT INTEGER) ON COMMIT PRESERVE ROWS;
SELECT COUNT(*) into myint FROM IRNI.A_RT_SENTINEL_LOG;

INSERT INTO SESSION.TEMP values (myint);

OPEN RC;

James Campbell

Thanks jacampbell,
i try your suggestion, by inputing into single var myint and it works fine.

Again,thx