× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. March 2006

RE: Create table not working....

Pros,
Thanks for all the responses. I tried removing the 'AS' and it does not compile. I can execute it perfectly the way it is as a SQL Script. I tried the command in STRSQL going directly to the green screen and it works just fine. I tried to display the SQL codes and I am getting 57006 for SQLSTT and '679-' for SQLCOD. I tried to insert this block of code but in vain.... 
    C/EXEC SQL
    C+ Set Option
    C+     Naming    = *Sys,
    C+     Commit    = *None,
    C+     UsrPrf    = *User,
    C+     DynUsrPrf = *User,
    C+     Datfmt    = *iso,
    C+     CloSqlCsr = *EndMod
    C/END-EXEC

any other suggestions???

Cheers,

Mike.



From: rpg400-l-request@xxxxxxxxxxxx
Reply-To: rpg400-l@xxxxxxxxxxxx
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 5, Issue 369
Date: Mon, 27 Mar 2006 11:06:23 -0600
MIME-Version: 1.0
Received: from mail.midrange.com ([69.3.23.26]) by bay0-mc10-f15.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 27 Mar 2006 09:06:29 -0800 Received: from rivendell.midrange.com (localhost [127.0.0.1])by mail.midrange.com (8.13.6/8.13.6) with ESMTP id k2RH6RxA000822;Mon, 27 Mar 2006 11:06:27 -0600 
X-Message-Info: vGzX0e+ktu4gX0CywIMLrHkpFA9XZ4cUtDBFLEAIOdw=
X-BeenThere: rpg400-l@xxxxxxxxxxxx
X-Mailman-Version: 2.1.7
Precedence: list
List-Id: RPG programming on the AS400 / iSeries <rpg400-l.midrange.com>
List-Unsubscribe: <http://lists.midrange.com/mailman/listinfo/rpg400-l>,<mailto:rpg400-l-request@xxxxxxxxxxxx?subject=unsubscribe> 
List-Archive: <http://archive.midrange.com/rpg400-l>
List-Post: <mailto:rpg400-l@xxxxxxxxxxxx>
List-Help: <mailto:rpg400-l-request@xxxxxxxxxxxx?subject=help>
List-Subscribe: <http://lists.midrange.com/mailman/listinfo/rpg400-l>,<mailto:rpg400-l-request@xxxxxxxxxxxx?subject=subscribe> 
Errors-To: rpg400-l-bounces@xxxxxxxxxxxx
Return-Path: rpg400-l-bounces@xxxxxxxxxxxx
X-OriginalArrivalTime: 27 Mar 2006 17:06:30.0289 (UTC) FILETIME=[CAB24810:01C651C0] 

Send RPG400-L mailing list submissions to
        rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
        http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
        rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
        rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful. 

Today's Topics:

   1. RE: GLOBAL TEMPORARY TABLE issues (Wilt, Charles)
   2. RE: GLOBAL TEMPORARY TABLE issues (Tyler, Matt)
   3. Create table not working.... (Mike Troxclaire)
   4. RE: Create table not working.... (Fleming, Greg (ED))
   5. RE: Create table not working.... (DeLong, Eric)
   6. Re: Create table not working.... (Michael_Schutte@xxxxxxxxxxxx)


----------------------------------------------------------------------

message: 1
date: Mon, 27 Mar 2006 07:58:06 -0500
from: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
subject: RE: GLOBAL TEMPORARY TABLE issues

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tyler, Matt
> Sent: Friday, March 24, 2006 11:38 AM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: GLOBAL TEMPORARY TABLE issues
<snip>
>
>    The solution we need must be compatible with our CMS product
> from Aldon.  So, during promotion time the model file object
> must exist
> because all promotions to the next environment must be done in batch.
> We do not want to build special exit program processing just for this
> one instance.  We could create the model file using SQL and add it to
> Aldon as a non-source based object, but as I said before our current
> standard is to use DDS only for files.
>

Matt,

Why would you need to create the object as non-source based in Aldon?

Unless you're on a really old version of Aldon, Aldon supports SQL
objects, *TABLE, *VIEW, *INDEX, *FUNCTN, *PROC, and *TRIGGER as pseudo
source objects.  You can check them out, edit, create, and promote them
just like DDS defined *FILEs.

HTH,

Charles





------------------------------

message: 2
date: Mon, 27 Mar 2006 07:48:30 -0700
from: "Tyler, Matt" <mattt@xxxxxxxxxxxxxx>
subject: RE: GLOBAL TEMPORARY TABLE issues

Yes I know, but our standards for source limit what type of source is
used to create objects (for the moment).  The only other way to get a
non-DDS based object into Aldon (for us) is to make it non-source based.


Thanks, Matt


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Monday, March 27, 2006 5:58 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: GLOBAL TEMPORARY TABLE issues

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tyler, Matt
> Sent: Friday, March 24, 2006 11:38 AM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: GLOBAL TEMPORARY TABLE issues
<snip>
>
>    The solution we need must be compatible with our CMS product
> from Aldon.  So, during promotion time the model file object
> must exist
> because all promotions to the next environment must be done in batch.
> We do not want to build special exit program processing just for this
> one instance.  We could create the model file using SQL and add it to
> Aldon as a non-source based object, but as I said before our current
> standard is to use DDS only for files.
>

Matt,

Why would you need to create the object as non-source based in Aldon?

Unless you're on a really old version of Aldon, Aldon supports SQL
objects, *TABLE, *VIEW, *INDEX, *FUNCTN, *PROC, and *TRIGGER as pseudo
source objects.  You can check them out, edit, create, and promote them
just like DDS defined *FILEs.

HTH,

Charles



--

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.




------------------------------

message: 3
date: Mon, 27 Mar 2006 10:37:23 -0600
from: "Mike Troxclaire" <mike_troxclaire@xxxxxxxxxxx>
subject: Create table not working....

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/



------------------------------

message: 4
date: Mon, 27 Mar 2006 11:59:27 -0500
from: "Fleming, Greg \(ED\)" <GFLEMING@xxxxxxxxxxxxxxxxxxxx>
subject: RE: Create table not working....

Mike,

That code will display your message whether the SQL succeeds or fails.
You can examine the SQL return codes in the SQLCOD and SQLSTT fields of
the SQLCA data structure, and condition your display accordingly.

Being somewhat new to embedded SQL myself, I don't see anything wrong
right off the bat, but if you put the program in debug and put the break
on your display message, you can examine the aforementioned codes and
see what you get.

Follow the "SQL Messages and Codes" link from the following page to
decipher their meaning:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/d
b2/rbafzmstcksql.htm



|-----Original Message-----
|From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
|On Behalf Of Mike Troxclaire
|Sent: Monday, March 27, 2006 11:37 AM
|To: rpg400-l@xxxxxxxxxxxx
|Subject: Create table not working....
|
|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.




------------------------------

message: 5
date: Mon, 27 Mar 2006 11:05:01 -0600
from: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
subject: RE: Create table not working....

Not sure.....  One of the most common of mistakes when starting with
embedded SQL is making sure to specify the correct level of commitment
control.  Most developers do not journal in their test libraries, so
commitment control should be set to *NONE on either the compile command or
in an SQL options block in the source member.

Could this be your problem?

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Mike Troxclaire
Sent: Monday, March 27, 2006 10:37 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Create table not working....


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.




------------------------------

message: 6
date: Mon, 27 Mar 2006 12:06:12 -0500
from: Michael_Schutte@xxxxxxxxxxxx
subject: Re: Create table not working....

Mike,

Have you tried doing this command in STRSQL?

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx



             "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.





------------------------------

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) digest 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.



End of RPG400-L Digest, Vol 5, Issue 369
****************************************


_________________________________________________________________
Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.