Adam,
I may be oversimplifying but I think the case clause will do what you want. Where you want the model field insert a case instead.
Select customer, order,
case
when model1 = 'Freebatteries' then model2
else model1
end,
case
when model1 = 'Freebatteries' then model1
else model2
end
From PurchaseFile
If 'Freebatteries' changes the statement would need to be changed though.
HTH,
Rick
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Adam West
Sent: Friday, September 05, 2008 2:12 PM
To: rpg400-l@xxxxxxxxxxxx
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.
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
midrange.com
