Re: how to create variable in sql procedure?

On Wed, Mar 14, 2018 at 1:35 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

Create Variable is very slow. Hope you are not calling this much.

Why use an SQL procedure instead of using an SQL RPG service program?

I am calling a SQL procedure with input parms.

in the SQL procedure I want to create a new table from a select statement,
where the "where" clause of that select uses the input parms:

CREATE or replace PROCEDURE demoProc(
in inCompanyCode char(2)
)

Create table steve1 as (
select * from custmast
where companyCode = inCompanyCode )
with data ;

But I cannot do this. Cannot have substitution variables in a create table
as statement.

So instead, I create a table in qtemp, copy the variables into that table,
then join to the temporary table in the Select statement:

create table qtemp/parms ( key char(1), companyCode char(2)) ;
insert into qtemp/parms ( key, companyCode ) values( '1', inCompanyCode ) ;

Create table steve1 as (
select a.* from custmast a
join qtemp/parms b on b.key = '1'
where a.companyCode = b.CompanyCode )
with data ;

But this does not work because of SQL restrictions on inserting data into
tables in QTEMP and tables that are not journaled.

I want to replace this temporary table with a declared variable:

create variable qgpl.parm_companyCode char(2) default inCompanyCode ;
Create table steve1 as (
select a.* from custmast a
where a.companyCode = qgpl.parm_CompanyCode )
with data ;