Solved

db2, sqlrpgle program, difficulties with sintax sql

Posted on 2011-03-07
3
811 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 35

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tsm library capacity monitor warning 5 183
I want coulmn name and value as output 18 66
SQL400 max size 5 104
What's the best to access MS Sequel Server data via ILE-RPG? 3 91
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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 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