How can I use the FOR EACH... WHERE against an array? 1

[Faelcu]

I'm trying to select records based on a field being equal to any of the elements in a hard-coded array and I seem to be brain-dead on this today. :~/ Can anyone give me some suggestions on how I can make this happen?

TIA

 

[sshowers]

Help us understand what you mean by "hard-coded array". Did you:

     def var v-array as char no-undo init "A,B,C,D,E".

Or, did you:

     def var v-array as char format "x" extent 5 no-undo.
     assign
        v-array[1] = "A"
        v-array[2] = "B"
          ...
        .

Because the solution is different for each.

 

[hjgoldstein]

Try using CASE. I can't remember the syntax - it's been a long time since I used it, but it should do the trick

Hope this helps.

 

[Faelcu]

I have the array defined as
ae-code as char format "x(3)" extent 54
init ["L01",
"D01",
"L09",..... etc.

I need to find all the records where a field's value equals the value in any of the elements of the array. Is there any way to do that in a WHERE clause?

Thanks

 

[sshowers]

Without better understanding the details of what you are really trying to accomplish and the size of the base tables I would, for clarity sake, probably approach your example something like:

     def var v-i as int  no-undo.
     def var v-l as logi no-undo.

     for each record no-lock:
        v-l = false.
        VAL: do v-i = 1 to 54:
           if record.field = ae-code[v-i]
           then do:
              v-l = true.
              leave VAL.
           end.
        end.
        if v-l then do:
           {rest of program logic here}
        end.
     end.

On the flip side, if your array is static and you define it differently, you might get away with something like:

     def var ae-code as char no-undo.
     ae-code = "L01,D01,L09,...".

     for each record no-lock
     where can-do(ae-code,record.field):
        {rest of program logic here}
     end.

Hope this helps,
sshowers

 

[jerryk]

I agree with the above suggestion. This isn't something that can be done with a temp-table, or shared temp-table?

 

[Faelcu]

Thanks for all the help on this one.

I made the array a list and used a lookup in the FOR EACH WHERE statement and things are working beautifully.

Thanks very much, everyone, for your help!