RE: SQL Create Table and Host Variables

["Mohammad, Tanveer" <TMohammad@xxxxxxxxxxx>]

I think here is the answer of your question.
        
                                Additional Message Information

 

        Message ID . . . . . . :                SQL0090
Severity . . . . . . . :                30              
        Message type . . . . . :                Diagnostic

 

        Message . . . . :   Host variable not permitted here.

        Cause . . . . . :   Host variable VAR is not allowed as used in
this           
          statement. Host variables are not allowed:

            -- In a CREATE VIEW, CREATE TABLE, or ALTER TABLE statement.

            -- In any interactive SQL statement when the Statement
processing value is 
          *RUN or *VLD.

            -- In an SQL statement processed by the RUNSQLSTM command.

            -- In an INSERT, UPDATE, DELETE, or DECLARE CURSOR statement
in REXX.      
        Recovery  . . . :   Do one of the following and try the request
again.         
            -- Specify either a constant or a column name to replace the
host          
          variable.  The colon indicates that the name that follows is a
host          
          variable.  Remove the colon to specify a column name.

            -- If in interactive SQL, set the statement processing value
to *SYN to    
 
More... 
        Press Enter to continue.

 

        F3=Exit                 F6=Print                F9=Display
message details                                        
        F10=Display messages in job log                 F12=Cancel
F21=Select assistance level         
 


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Carel Teijgeler
Sent: Tuesday, September 20, 2005 9:06 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Create Table and Host Variables

Dave,

Why not have a template file created once and when used make a DUPOBJ in
the required library (QTEMP in your example); similar to the *OUTFILE
technique used with IBM supplied commands.

And if you want to create a CSV file, why not run embedded SQL and write
to the IFS directly, putting the field seperators in the proces?

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 20-9-05 at 11:36 Smith, Dave wrote:

>When I do a:
>
>CREATE TABLE  QTEMP.TableName AS
>(
>SELECT            Fields
>FROM               File
>WHERE            Fielda = :Variable
>) with Data
>
>I get an error message that I can not use a host variable in this
>statement.    I tried it in both embedded RPG and in a SQL procedure.
>
>So instead I've been doing  
>
>CREATE TABLE  QTEMP.TableName AS
>(
>SELECT            Fields
>FROM               File
>) with No Data;
>
>INSERT INTO QTEMP.TABLENAME
>SELECT            Fields
>FROM               File
>WHERE            Fielda = :Variable

>
>Now I get my table with the data I need but I need to maintain my
select
>statement in two spots...On the CREATE TABLE and on the INSERT.
>Anybody have a better method of using SQL to create a temporary file.
>In this case I'm using an email package to email the file off in a CSV
>file and then deleting the file.