In the same one query, there are several data items which are listed below, I need to find all IDs that contains a code 'A', 'A' can be in any comments from Comment1 to Comment20.
ID Comment1 Comment2 Comment3 ... Comment20
001 A D E ... B
002 D F A ... C
003 G A C ... D
...
00n
Any better methods to find all IDs that contains 'A'?
Thanks.
What I thought is separate them into several queries, and each query include only one Comment, then filter the Comment and leave "A" value only. Do the same thing with other queries, and finally outer join the queries.
But there are 20 comment code. That take lots of steps. I think there is a better method to solve it.
You can definitely do an OR in a filter:
[Comment 1] contains 'A'
or [Comment 2] contains 'A'
or [Comment 3] contains 'A'
Since you're doing a contains or a like, it's going to be a pretty slow query. Are the comment fields nullable?
If they are, try this:
coalesce([Comment1],'') + coalesce([Comment2],'') +coalesce([Comment3],'') +coalesce([Comment4],'') contains 'A'
If not, do the same thing but without the coalesce.
I'd even consider adding that concat field to the ETL.
All these methods work very well. Even use OR in filter runs fast. Thank you.
Quote from: CognosPaul on 20 Mar 2019 09:32:06 PM
You can definitely do an OR in a filter:
[Comment 1] contains 'A'
or [Comment 2] contains 'A'
or [Comment 3] contains 'A'
Since you're doing a contains or a like, it's going to be a pretty slow query. Are the comment fields nullable?
If they are, try this:
coalesce([Comment1],'') + coalesce([Comment2],'') +coalesce([Comment3],'') +coalesce([Comment4],'') contains 'A'
If not, do the same thing but without the coalesce.
I'd even consider adding that concat field to the ETL.