Author Topic: Help needed!!! find value in different columns  (Read 492 times)

Offline helloworld

  • Associate
  • **
  • Join Date: Mar 2019
  • Posts: 3
  • Forum Citizenship: +0/-0
Help needed!!! find value in different columns
« on: 20 Mar 2019 02:56:40 pm »
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.
« Last Edit: 20 Mar 2019 03:24:32 pm by helloworld »

Offline helloworld

  • Associate
  • **
  • Join Date: Mar 2019
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: Help needed!!! find value in different columns
« Reply #1 on: 20 Mar 2019 04:58:43 pm »
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.

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,822
  • Forum Citizenship: +262/-1
    • Paul's Cognos Blog
Re: Help needed!!! find value in different columns
« Reply #2 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.

Offline helloworld

  • Associate
  • **
  • Join Date: Mar 2019
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: Help needed!!! find value in different columns
« Reply #3 on: 21 Mar 2019 10:01:44 am »

All these methods work very well. Even use OR in filter runs fast. Thank you.

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.

 



       
Twittear