Re: Can this be done in SQL?

["Charles Wilt" <charles.wilt@xxxxxxxxx>]

In addition, it would be pretty easy to build a view that "pivots" the
normalized data back into the form you're using now.


Charles


On Fri, Sep 5, 2008 at 3:59 PM,  <rob@xxxxxxxxx> wrote:
> The first step is to always take these BS non normalized files and
> normalize them.  So before you even think about posting them in to your
> system, split them up into multiple data files.
> Instead of one customer purchage file you have two (or more).  One has a
> unique identifier with all the header info.  The second will have the
> unique identifier, maybe a line number and ONE item number.  Then you can
> sort that to your hearts content.
>
> Want to verify that they all got their free batteries?
>
> Then, who didn't get them?
> Select * from OrderLine
> where item='MODELA'
> and (orderNbr, lineNbr) not exists
> (select orderNbr, lineNbr from OrderLine where item='MODELZ')
>
> something like that.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Adam West <adamster@xxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 09/05/2008 03:35 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> rpg400-l@xxxxxxxxxxxx
> cc
>
> Subject
> Can this be done in SQL?
>
>
>
>
>
>
> HI I have a file that it is a customer purchase file, they hold up to 5
> items that were purchased and each record has the five
> items,model1, model2, model3, etc most of the time the last 4 are blank.
>
> The need arose to produce a picking list by model number ordered rather
> than by invoice number which was easy enough to do, by making a logical on
> model1. however, there are sometimes promotions where if you purchase
> model A you get a free Model Z. This model Z is often gets placed as
> Model1 which obviously throws off the sort because the main partof the
> order would be model2 in that case.
>
> I was wondering if in SQL I could do a shift if model1 = say
> 'Freebatteries' make model1 = model2 and model2=model1.
>
> I have little control of this incoming data as it is a feed from an
> outsourced web site. So I have to do the reshuffling.
>
> it could be done via a small RPG program but maybe in SQL it is faster and
> something to learn for me.
>
>
>
>
>
> --
> 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.