If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Help needed!!! find value in different columns

Started by helloworld, 20 Mar 2019 02:56:40 PM

Previous topic - Next topic

helloworld

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.

helloworld

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.

CognosPaul

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.

helloworld


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.