RE: Slight-Modification : Paramater Markers -- RPG400-L

In the spirit of re-reading...  I noticed that the create table is not
allowed with parm markers... So I went the dynamic route, and it solved the
problem...

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Hatzenbeler, Tim
Sent: Friday, April 07, 2006 12:49 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Slight-Modification : Paramater Markers

I just re-read what I posted, and I used the wrong using statement...

Please replace the bottom one with...

     C/EXEC SQL
     C+ Execute S1 using :strdate, :enddate, :Warehse
     C/END-EXEC
      
I tried hard coding the between values, to remove potential issues.. but
even with only one PM on the warehouse field I was getting the same error..

Thanks, Again... tim



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Hatzenbeler, Tim
Sent: Friday, April 07, 2006 12:36 PM
To: RPG programming on the AS400 / iSeries
Subject: Paramater Markers

sqltbl = 
'create table devtimh/job1 as                                '+

'  (                                                         '+

'  SELECT COMPANY, WAREHOUSE, ITEM_CODE, SIZE_CODE, UOm,sum  '+

'  (RECEIVED_QTY )/12 as monthusg                            '+

'  FROM mmcust120/usagecube WHERE (USAGE_DATE_SQL            '+

'  between ? and ?) and warehouse = ?                        '+

'  GROUP BY COMPANY, WAREHOUSE, ITEM_CODE,                   '+

'  SIZE_CODE,uom                                             '+

'  ) with data                                               ';



     C/EXEC SQL
     C+ Prepare S1 from :sqltbl
     C/END-EXEC

     C/EXEC SQL
     C+ Execute S1 using :Warehse
     C/END-EXEC


Gives me this error...

I'm using the "SUM" function, but its not in the job log as a no-no...

Am I missing something?  Thanks,  Tim



Message ID . . . . . . :   SQL0418       Severity . . . . . . . :   30

 Message type . . . . . :   Diagnostic

 Date sent  . . . . . . :   04/07/06      Time sent  . . . . . . :
12:29:10   
 

 Message . . . . :   Use of parameter marker not valid.

 Cause . . . . . :   Parameter markers are not allowed:

     -- In the SELECT clause of the statement string to be prepared.

     -- As a value in a VALUES INTO statement.

     -- As an operand of a concatenation operation.

     -- As the operand of a scalar function.  If the scalar function is
VALUE,  
   COALESCE, IFNULL, MIN, MAX, LAND, LOR, or XOR, then at least one of the

   arguments must not be a parameter marker.

     -- As the left operand of the LIKE predicate.

     -- As the operand of a unary minus.

     -- In an SQL statement in embedded SQL or in interactive SQL.

     -- In an EXECUTE IMMEDIATE statement.

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

     -- In a statement processed by the RUNSQLSTM command.

     -- In a blocked INSERT statement.

 Recovery  . . . :   Ensure parameter markers are only specified where they
are 
   allowed.  A CAST specification can be used in many situations. Correct
any   
   errors.  Try the request again.