Re: SQL needed for this tricky problem

["Brian Johnson" <brian.johnson.mn@xxxxxxxxx>]

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,Field14,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.