RE: SQL needed for this tricky problem -- RPG400-L

I second this....

Nice solution Brian.

Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, November 12, 2007 4:01 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL needed for this tricky problem

VERY clever
VERY abstract
I was attempting to use CTE's with CASE selects Needless to
say, I was failing

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 11/12/2007 03:56:17 PM:

I'll take a stab at it. All I can claim is that it is

valid syntax...

update afile a
set a.count = ( select length(
trim(b.f6) || trim(b.f7) || trim(b.f8) || trim(b.f9) ||
trim(b.f10) || trim(b.f11) || trim(b.f12) || trim(b.f13) ||
trim(b.f14) || trim(b.f14) )
from bfile b where b.f1 = a.f1 and b.f2 = a.f2 and b.f3 = a.f3 and
b.f4 = a.f5 and b.f5 = a.f5 )

On Nov 12, 2007 2:20 PM, donna lester

<donna_lester_ny@xxxxxxxxx> wrote:

Hello All,
My problem is as below:

====================================================================

When
Field1 of AFILE Equal to Field1 of BFILE
Field2 of AFILE Equal to Field2 of BFILE
Field3 of AFILE Equal to Field3 of BFILE
Field4 of AFILE Equal to Field4 of BFILE
Field5 of AFILE Equal to Field5 of BFILE
I will have to see if there are any entries in

Field6,Field7,Field8,Field9,Field10,Field11,Field12,Field13,Fi
eld14,Field15

of BFILE...

(All these 10 fields from Field6 to Field15 are 1 char

defined) If

there are entries in all 10 fields then the count is 10

If there are

entries in 9 fields then the count is 9 If there are entries in 8
fields then the count is 8 If there are entries in 7

fields then the

count is 7 If there are entries in 6 fields then the

count is 6 If

there are entries in 5 fields then the count is 5 If there are
entries in 4 fields then the count is 4 If there are entries in 3
fields then the count is 3 If there are entries in 2

fields then the

count is 2 If there are entries in 1 fields then the

count is 1 If

there are no entries in any of the fields then the count is 0...
what I want to do is
I want to insert this count field in the AFILE when each record of

AFILE

is equal to its matching record in BFILE

based on the below conditions
Field1 of AFILE Equal to Field1 of BFILE
Field2 of AFILE Equal to Field2 of BFILE
Field3 of AFILE Equal to Field3 of BFILE
Field4 of AFILE Equal to Field4 of BFILE
Field5 of AFILE Equal to Field5 of BFILE
All help is greatly appreciated!

Thanks,

Donna.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
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.

--
Brian Johnson
brian.johnson.mn@xxxxxxxxx
--
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 e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.