Problem about executing same query on number of tables

mangsk · New User Joined: 14 Mar 2005 Posts: 2

Hi All,
My requirement is - I've to execute the same query
e.g. say
EXEC SQL
SELECT COUNT (*)
INTO :WS-COUNT
FROM table-name
on 50 different tables in a COBOL program and process the count after each time.
Is there any way to do it other than writing the same query 50 times? The table names I've already stored in an array. I tried out defing a variable as WS-TBL-NAME in which I'll move the table name from array and use this variable in FROM. But it doesn't work.
Please advice.

Thanks.

priya · Posted: Sun Mar 20, 2005 1:39 am Post subject:

Why not you try Dynamic SQL to do this.

mangsk · New User Joined: 14 Mar 2005 Posts: 2

The requirement doesn't allow us to use dynamic SQLs.

regds,
Mangsk

ovreddy · Posted: Mon Mar 21, 2005 12:41 pm Post subject:

Hi All,

As I know EXECUTE SQL IMMEDIATE statement wont work with SELECT statement. But still you can use EXECUTE SQL PREPARE to do this.

I hope the following will work...

MOVE "SELECT count(*) into :WS-COUNT FROM ?" to sql-to-execute.

EXEC SQL
PREPARE FLSQL FROM :Sql-to-execute;
END-EXEC.

MOVE "TABLE1" TO WS-PARM1.

EXEC SQL
EXECUTE FLSQL USING :WS-PARM1.
END-EXEC.

I am not sure that above code will work. So please Test it and let me know the result.

Thanks,
Reddy.