Re: creating a file via SQL

Alan,
Try adding WITH DATA at the end of your SQL statement.

cheers,

Jake.

On 10/3/06, Alan Shore <AlanShore@xxxxxxxx> wrote:

Thanks Rob
I tried using your example, but it came back with a weird error
Here is my non-simplified SQL

CREATE TABLE FILEC( BCMPY, BAMMDD, BAYY, BAXXX, ORDNUM,
"SOURCE", SDCHANNL, SKUNO, SUFFIX, ITMQTY, PRICE, ITMEXT, ITMNAM,
ICCAT, ICSUB) AS (SELECT A.BCMPY, A.BAMMDD, A.BAYY, A.BAXXX,
A.ORDNUM, A."SOURCE", A.SDCHANNL, A.SKUNO, A.SUFFIX, A.ITMQTY,
A.PRICE, A.ITMEXT, A.ITMNAM, COALESCE(B.SLSCTG, 0),
COALESCE(B.SLSSORT#, 0) FROM QTEMP/SLSTOPPF A LEFT OUTER JOIN
SLSTOPITMP B ON  (A.SUFFIX || A.SKUNO) = (B.SUFFIX || B.SKUNO))

The error that gets returned is
Token <END-OF-STATEMENT> was not valid. Valid tokens: WITH DEFINITIO
Any idea what is happening, or what I am missing?

Alan
rpg400-l-bounces@xxxxxxxxxxxx wrote on 10/03/2006 11:57:24 AM:

> You left out the example of what if FileA has item number 000002 and
there
> is no 2 in FileB.  But my example should fill that up with a zero in
> category and subcategory.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Alan Shore <AlanShore@xxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 10/03/2006 11:55 AM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
> "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>,
> rpg400-l-bounces@xxxxxxxxxxxx
> Subject
> RE: creating a file via SQL
>
>
>
>
>
>
> FileA
> Order number      2001
> Item number       000001
> Quantity          20
> Category          0
> Sub category      0
>
> FileB             Record      Record
> Item number       000001      000001
> Category          0001        0094
> Sub category      0010        0026
>
> FileC would contain the following records
> FileA             Record            Record
> Order number      2001              2001
> Item number       000001            000001
> Quantity          20                20
> Category          0001              0094
> Sub category      0010              0026
>
> One record in FileA creates two records in FileB due to the item 000001
> being in FileB twice.
>
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
> rpg400-l-bounces@xxxxxxxxxxxx wrote on 10/03/2006 11:43:19 AM:
>
> > Can you post some data and what you want as final result? Because I
miss
> > something here, but don't know what.
> >
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> > Sent: Tuesday, October 03, 2006 11:20 AM
> > To: RPG programming on the AS400 / iSeries
> > Subject: creating a file via SQL
> >
> > Morning all
> > I am giving myself a headache in trying to solve a problem with SQL
> > I've tried looking through the archives, but I am not really too sure
> > what
> > I am looking for.
> > Anyway, if anyone can help me,
> >
> > The problem is as follows:-
> > I have 2 files, in their simplistic form
> > FileA
> > Order number
> > Item number
> > Quantity
> > Category
> > Sub category
> >
> > FileB
> > Item number
> > Category
> > Sub category
> >
> > In FileB, Item number is NOT unique. Item number within Category IS
> > unique.
> > For example Item number 000001 can be in Category 0001 AND in Category
> > 0094
> > What I want to happen is that for an order that contains item number
> > 000001, I want a file (FileC) that contains 2 records from the
original
> > 1.
> > One record with Category 0001 and the other record with category 0094.
> > FileA to FileC is a one to many ratio.
> > The other problem is if the item number does NOT exist on FileB, FileC
> > should contain one record with 0 category.
> >
> > I think that's it.
> > If anyone can help, or point me in the right direction, I would be
VERY
> > grateful
> >
> > 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 message contains information proprietary to our company. It is
> > intended to be read only by the individual or entity named above or
> > their designee.
> > Any distribution of this message or the information contained herein
> > without written permission from our company is strictly prohibited.
> > If the reader of this message is not the intended recipient or an
> > agent responsible for delivering it to the intended recipient, you
> > are hereby notified that you have received this document in error
> > and that any review, dissemination, distribution, or copying of this
> > message is strictly prohibited. If you have received this
> > communication in error, please notify us immediately by e-mail, and
> > delete the original message.
> >
> >
> >
> > --
> > 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.
>
>
> --
> 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.