Solved

db2, sqlrpgle program, difficulties with sintax sql

Posted on 2011-03-07
3
809 Views
Last Modified: 2012-05-11
Hi,
I'm programming with sqlrpgle,
I have some syntax problem..
look at this:
C*                                                                      
c     *entry        plist                                                
c                   parm                    fromart          13          
c                   parm                    toart            13          
C/EXEC SQL                                                              
c+ with tmp as                                                          
c+ ( select * from qs36f/artordimp                                      
c+ where i$pn between :fromart and :toart)                              
c+                                                                      
C+ create table qs36f/ordcli as                                          
C+ (tmp)                                                                
C+ with data                                                            
C/END-EXEC                                                              
C                   SETON                                        LR      

How to use "with" statment and "create_table" in the same program?
I decide to use "with statment" only beacuse the compiler says
that I cannot use parameters in "create table" statment,
How to make this program work?
0
Comment
Question by:bobdylan75
3 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 35059303

The following works beautifully for me on V6R1.

HTH,
DaveSlash



create table qtemp/deleteme as (           
  select *                                 
    from qtemp/tmtiers                     
   where TIERLETTER in ('S', 'M', 'F', 'C')
  with nc
) with data

Open in new window

0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 35061150
Try using dynamic sql to get around the variable limitation:


d sqlString       s          32000a   varying 

c     *entry        plist                                                
c                   parm                    fromart          13          
c                   parm                    toart            13          

c                   eval      sqlString = 'with tmp as ' +                                                          
c                             '(select * from qs36f/artordimp ' +                                      
c                             ' where i$pn between ' + 
c                               fromart + ' and ' + toart + ') ' +                              
C                              'create table qs36f/ordcli as (tmp)' +
c                              'with data'                                                       

c/exec sql
c+                  execute immediate :sqlString
c/end-exec

Open in new window

0
 

Author Comment

by:bobdylan75
ID: 35066855
Thank you Gary.. I will try it in the next hour...
see you later...
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now