I created a script in which I create a stored procedure in which I use an instruction
CREATE GLOBAL TEMPORARY TABLE ...
and then I perform operations on the tables

I use RUNSQLSTM command to execute script:
if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

is there a way in which I don't have to create the temporary table first ?

Thanks

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, June 14, 2019 at 6:35:42 AM UTC-4, Marco Franchini wrote:

I created a script in which I create a stored procedure in which I use an instruction
CREATE GLOBAL TEMPORARY TABLE ...
and then I perform operations on the tables

I use RUNSQLSTM command to execute script:
if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

is there a way in which I don't have to create the temporary table first ?

Try setting the ERRLVL parameter of RUNSQLSTM higher?
--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I use ERRLVL=30

Try setting the ERRLVL parameter of RUNSQLSTM higher?
--buck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, 14 June 2019 11:35:42 UTC+1, Marco Franchini wrote:

Hi

in abstract, a global temporary table is nothing more than a subset of rows copied against a new table in qtemp. Saying that, you may want to keep your current solution using temporary tables, but taking advantage of the "dynamic sql". Here's an example
taken from one of our procedures:

DECLARE STMT CHAR ( 2000 ) ;
DECLARE APX CHAR ( 1 ) DEFAULT '''' ;

SET STMT =
'declare global temporary table w_scan_ser# as (
select distinct c.table_name, c.column_name
from qsys2.syscolumns c join qsys2.systables t
on (t.table_name=c.table_name and t.table_schema=c.table_schema and t.table_type=' || APX || 'P' || APX ||
') where c.table_schema=' || APX || PLIB || APX || ' and trim(c.column_name) like ' || APX || '%SER#' || APX ||
') with data with replace' ;

PREPARE DCLSTMT FROM STMT ;
EXECUTE DCLSTMT ;

using dynamic sql the system will not check whether or not the temporary table exists.

hope this helps

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi
when u use the temporary table did you address it as session.mytemptab, qtemp.mytemptab or just using library list?

e.g. I just ranit against ASC SQL client and it worked.

declare global temporary table MyTemp as (
select * from prodfil.f56232 fetch first 10 rows only) with data with replace;

update qtemp.mytemp set xddoco=xddoco;
update session.mytemp set xddoco=xddoco;

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 6/14/2019 3:35 AM, Marco Franchini wrote:

I created a script in which I create a stored procedure in which I use an instruction
CREATE GLOBAL TEMPORARY TABLE ...
and then I perform operations on the tables

I use RUNSQLSTM command to execute script:
if the temporary table does not exist RUNSQLSTM return error TABLE xxx not found and the stored procedure is not created
if I create temporary table in QTEMP and I execute script the command ends successfully and the stored procedure is created

is there a way in which I don't have to create the temporary table first ?

Thanks

I run into the same problem. You can work around this by adding a step to
the RUNSQLSTM source to create the temporary table first (you can have
multiple statements in a RUNSQLSTM source member.)

Source:

-- Stmt 1
declare global temporary table my_table
[table definition statement];

-- Stmt 2
create or replace procedure myschema.myprocedure
[procedure definition statement(s)]

If this is submitted to batch, you won't need to worry about the temporary table already existing. If you issue RUNSQLSTM interactively multiple
times, you could handle the possibility of the temporary table already
existing by something like this:

-- Stmt 1
begin
declare continue handler for sqlstate '42710'
truncate qtemp.my_table;
declare global temporary table my_table
[table definition statement];
end;

SQLSTATE 42710 is issued if you try to create an object that already
exists. If the temporary table exists, just clear it (TRUNCATE). The
compound statement will then end, and proceed on to Stmt 2. An alternative would be to switch from DECLARE GLOBAL TEMPORARY TABLE to CREATE OR REPLACE TABLE, which would allow you to dispense with the compound statement:

-- Stmt 1
create or replace table qtemp.my_table
[table definition statement]
on replace
delete rows;

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)