Re: Can this be done in SQL?

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.