Re: SQL block insert

[Alan Shore <AlanShore@xxxxxxxx>]

Hi Jeff,
the insert is based upon the order of the fields in the output files record
and NOT field name to filed name

for example, one imbedded sql I have just created (and still modifying) is
as follows

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

where the definition of the output file is
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



                                                                       
             "Jeff Crosby"                                             
             <jlcrosby@dilgard                                         
             foods.com>                                                 To
             Sent by:                  "'RPG programming on the AS400 /
             rpg400-l-bounces@         iSeries'" <rpg400-l@xxxxxxxxxxxx>
             midrange.com                                               cc
                                                                       
                                                                   Subject
             08/01/2006 01:32          SQL block insert                
             PM                                                        
                                                                       
                                                                       
             Please respond to                                         
              RPG programming                                          
              on the AS400 /                                           
                  iSeries                                              
             <rpg400-l@midrang                                         
                  e.com>                                               
                                                                       
                                                                       




I'll bet you guys are getting tired of me asking SQL questions, aren't you?
<g>

Up to now, all my embedded SQL has been cursor driven.  However I have one
now where I want to join 2 files and output the result set into another
file.  Since SQL is set-at-a-time, I didn't see a reason to use a cursor,
just slam the whole result set out there.

The statement I'm wanting to execute is this:

Insert into AUTHITEM
  (Select c.*, i.* From NXCHNBIL C
   Join NXITMMST I
   On c.ITNBR = i.ITNBR
   Where c.ACREC = 'A' and
         c.CUSNR = 0509 and
         c.CHAUT in ('2', '8', '9'))

It gives me an error saying field QSUN2 doesn't map properly.  My guess is
that it is mapping the fields based on order, and I want to map based on
field name.  Am I right on the error?  And is there a way to map by field
name without specifying each?

File AUTHITEM has 100+ fields, some from each of the other 2 files, but not
nearly all fields.

Thanks.

--
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the opinion of my
company.  Unless I say so.


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