RE: embedded SQL....

["Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx>]

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