Need help with coding the following in a precise manner

[ratnaparakhee]

Hi All

I have a list of values that i am checking in multiple fields. I have a hit if these values are present in any of the fields. Initially i coded it as below-

and ( a.dx1 in &dx.

or a.dx2 in &dx.
or a.dx3 in &dx.
or a.dx4 in &dx.
or a.dx5 in &dx.)

The issue was that i got result in which there were values other than my list in the fields DX2 to DX5 as they are separated by 'OR'

To fix this issue is re programmed as below-

and a.dx1 in &dx.
and (a.dx2 = ' ' or a.dx2 in &dx.)
and (a.dx3 = ' ' or a.dx3 in &dx.)
and (a.dx4 = ' ' or a.dx4 in &dx.)
and (a.dx5 = ' ' or a.dx5 in &dx.)

Now my issue is solved but i do not like the way it is programmed. 

Is there a better way to write this? I mean a function or something that will ensure that either dx2-dx5 is a blank/missing value and if its not missing then its a value that i have listed in the macro variable &dx.

Note: As per business logic DX1 is always populated but dx2-dx5 can be blank also and its acceptable.

Thanks

Bhawna R.

[LinusH]

If you could share some input data, and exemplify the different scenarios it would be easier to see possible solutions.

[ratnaparakhee]

Hi @LinusH
I guess i got my solution. Want to still thank you to take out time and looking into my problem.
Regards
Bhawna

[FreelanceReinh · Accepted solution]

Hi @ratnaparakhee,

You could add the missing value to the list &dx (if that is acceptable wherever &dx is used) and then write

and a.dx1 in &dx & a.dx1 ne ' '
and a.dx2 in &dx
and a.dx3 in &dx
and a.dx4 in &dx
and a.dx5 in &dx

Other than that I don't see much potential for simplification.

[ratnaparakhee]

Hi @FreelanceReinhard

That thought did cross my mind but wasn't sure if adding a missing value to the list was a good idea or not.
Now that you mentioned it, I will give it a try and compare if the output differs from my original one.
Thanks for your help.