Re: Create table not working.... -- RPG400-L

Hi Mike - I'm not too sure if this will rectify your problem but how about
including
C/EXEC SQL
C+ Set Option Commit = *None
C/END-EXEC

at the beginning


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


                                                                       
             "Mike Troxclaire"                                         
             <mike_troxclaire@                                         
             hotmail.com>                                               To
             Sent by:                  rpg400-l@xxxxxxxxxxxx           
             rpg400-l-bounces@                                          cc
             midrange.com                                              
                                                                   Subject
                                       Create table not working....    
             03/27/2006 11:37                                          
             AM                                                        
                                                                       
                                                                       
             Please respond to                                         
              RPG programming                                          
              on the AS400 /                                           
                  iSeries                                              
             <rpg400-l@midrang                                         
                  e.com>                                               
                                                                       
                                                                       




Pro's,

I am trying to execute a simple SQL embedded RPG program and it compiles
and
runs fine(It displays the final message 'PHYS FILE CREATED.....'  but I am
not finding the physical file it is supposed to create. I do not know what
I
am doing wrong. Any help is much appreciated. Please bear with me, I am
still new to RPG so I might be doing something really      wrong. This code

executes just fine when I run it as a standalone SQL script. The only
difference is that library name and file name are separated by a '.'
instead
of '/'.  Here is the code that I am trying to execute....
==============================================================
     D EXTERNALDS    E DS                  EXTNAME(MAR_2006)
     C/Exec Sql
     C+ CREATE TABLE TESTING/TTABL AS
     C+ (select CSTEQUIFAX.CSTNUM, CSTMST.CSNAME,
     C+ CSTMST.CSSTCD, CSTMST.CSADD2, CSTMST.CSCTST,
     C+ CSTMST.CSZIPA, CSTMST.CSPHON, CSTMST.CSDELT,
     C+ CSTMST.CSDNB2, CSTMST.CSDTAD, CSTMST.CSDELD,
     C+ CSTMST.CSCRLT, CSTMST.CSHCRD, CSTMST.CSDTMY,
     C+ CSTMST.CSLPYA, CSTMST.CSDTPY, CSTMST.CSTOTB,
     C+ CSTMST.CSBL30, CSTMST.CSBL60, CSTMST.CSBL90,
     C+ CSTMST.CSBL99, CSTMST.CSARTM, ARTERM1.ARTFDS,
     C+ CSTUDD.CSUD40, (CSTMST.CSBLCU+CSTMST.CSBLFU)
     C+ AS TOTAL,
     C+ (CSTMST.CSBL30+CSTMST.CSBL60+CSTMST.CSBL90+
     C+ CSTMST.CSBL99) AS TOTAL_PAST_DUE,CSTMST.CSHIST,
     C+ MAXINVOICE.MAXARINET, MAXINVOICE.ARDATE,
     C+ CSTUDD.CSUD03,CSTSHP.CSHP3, CSTSHP.CSHP6,
     C+ CSTSHP.CSHPZA from ec30data/cstequifax as
     C+ cstequifax left outer join ec30data/cstmst
     C+ as cstmst on cstequifax.cstnum = cstmst.cstnum
     C+ left outer join ec30data/cstudd as cstudd
     C+ on cstequifax.cstnum = cstudd.cstnum left
     C+ outer join ec30data/cstshp as cstshp on
     C+ cstequifax.cstnum = cstshp.cstnum left outer
     C+ join ec30data/maxinvoice as maxinvoice on
     C+ cstequifax.cstnum = maxinvoice.arcnum left
     C+ outer join ec30data/arterm1 as arterm1 on
     C+ cstmst.csartm = arterm1.artftc where
     C+ (cstmst.csctst like '%,%' and cstshp.cshp6
     C+ like '%,%'  AND CSTMST.CSARTM NOT LIKE '% %')
     C+ and (CSTMST.CSDELT = '' or CSTMST.CSDELT = 'I'
     C+ or CSTMST.CSDELD > 20051218) GROUP BY
     C+ CSTEQUIFAX.CSTNUM, CSTMST.CSNAME, CSTMST.CSSTCD,
     C+ CSTMST.CSADD2, CSTMST.CSCTST, CSTMST.CSZIPA,
     C+ CSTMST.CSPHON, CSTMST.CSDELT, CSTMST.CSDNB2,
     C+ CSTMST.CSDTAD,CSTMST.CSDELD, CSTMST.CSCRLT,
     C+ CSTMST.CSHCRD, CSTMST.CSDTMY,CSTMST.CSLPYA,
     C+ CSTMST.CSDTPY, CSTMST.CSTOTB, CSTMST.CSBLCU,
     C+ CSTMST.CSBLFU, CSTMST.CSBL30, CSTMST.CSBL60,
     C+ CSTMST.CSBL90,CSTMST.CSBL99, CSTMST.CSHIST,
     C+ MAXINVOICE.MAXARINET, MAXINVOICE.ARDATE,
     C+ CSTUDD.CSUD03, CSTUDD.CSUD40, CSTSHP.CSHP3,
     C+ CSTSHP.CSHP6, CSTSHP.CSHPZA, ARTERM1.ARTFDS,
     C+ CSTMST.CSARTM) WITH DATA
     C/End-Exec
      /free
         dsply 'PHYS FILE CREATED, END OF PROGRAM';
         *inlr = *on;
      /end-free
============================================================

Cheers,

Mike.

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.