RE: embedded SQL....

Check for SQLCOD and SQLSTT after the PREPARE & then again after the
EXECUTE.  If the PREPARE fails the EXECUTE will too.  Chances are one of
the two (if not both) are failing.  The SQLCOD you can look up in the
QSQLMSG message file the message ID will be, for example SQLCOD = 0084
the message ID would be SQL0084...


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, July 31, 2006 11:40 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: embedded SQL....


Sorry - slip of the lip
The file DOES exist, and I want to insert data into that file.
The file layout is as follows:-
Name        Type        Length      Dec         Loc
BCMPY             A           1                 1
SOURCE            A           8                 2
SDCHANNL          A           1                 10
BAMMDD            S           4     0           11
BAYY              S           2     0           15
SKUNO             A           6                 17
SUFFIX            A           4                 23
ITMQTY            P           8     0           27
ITMEXT            P           10    2           32
ITMNAM            A           30                38
ICCAT             S           2     0           68
ICSUB             S           2     0           70



Alan Shore

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


 

             "Holden Tommy"

             <Tommy.Holden@hca

             healthcare.com>
To 
             Sent by:                  "RPG programming on the AS400 /

             rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>

             midrange.com
cc 
 

 
Subject 
             07/31/2006 12:26          RE: embedded SQL....

             PM

 

 

             Please respond to

              RPG programming

              on the AS400 /

                  iSeries

             <rpg400-l@midrang

                  e.com>

 

 





Your SQL doesn't create a file...it inserts data into an existing file.
If you WANT to create a file try:

Sqlstatement = 'CREATE TABLE QTEMP/SLSTOPPF AS (SELECT BCMPY,SOURCE,''
'',BAMMDD,BAYY, SKUNO,SUFFIX,0,0,'' '',0,0 FROM ORDDETP WHERE BCMPY
IN ('P','Q','V','W','9')) WITH DATA'

That will create a file named QTEMP/SLSTOPPF with the data in the select
statement.



Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, July 31, 2006 11:23 AM
To: RPG programming on the AS400 / iSeries
Subject: embedded SQL....

Hi everyone
I am trying to create a file using embedded SQL, but I am having trouble
in
creating the new file.
Here is my code, can anyone please tell me what I am doing wrong?

 c     Selectdata    begsr
  *
 c                   eval      Sqlstatement = 'INSERT INTO SLSTOPPF '
+
 c                             '(SELECT BCMPY, SOURCE, ' + Quote + ' '
+
 c                             Quote +  ', BAMMDD, BAYY, SKUNO, SUFFIX,
' +
 c                             '0, 0, ' + Quote + ' ' + Quote + ', 0, 0'
+
 c                             ' FROM ORDDETP ' + %trim(WhereClause) +
')'
  *

 c/EXEC SQL
 c+ prepare sel from :Sqlstatement
 c/END-EXEC
 c/EXEC SQL
 C+ EXECUTE sel
 c/END-EXEC
  *
 c                   endsr

The field Whereclause is built depending upon what parameters the user
has
chosen.
Using STRDBG, the field Sqlstatement can look like this

INSERT INTO SLSTOPPF (SELECT BCMPY, SOURCE, ' ', BAMMDD, BAYY,
 SKUNO, SUFFIX, 0, 0, ' ', 0, 0 FROM ORDDETP WHERE BCMPY
IN ('P','Q','V','W','9'))

Any help, direction would be greatly appreciated


Thanks in advance



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
--
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) 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.