Author Topic: Filter gives error when using IN with multiple values  (Read 965 times)

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 233
  • Forum Citizenship: +6/-3
    • Invisi - Vision on Information
Filter gives error when using IN with multiple values
« on: 04 Oct 2017 06:54:00 am »
I have an issue in a report where I think the source lies in the framework, so I post it as a Framework issue:

In a report (relational package) I have a filter on one item. If I say in the filter that [data item] = 'X', it works. When I say [data item] IN ('X') it also works. When I say [data item] in ('X','Y') it fails with an error:

Code: [Select]
QE-DEF-0261 RQP - Parsing text: [Account Country Code] in ('AT','BE','BG','CZ','CY','DE','DK','EE','ES','FI','FR','GB','GR','HR','HU','IE','IT','LT','LU','LV','MT','NL','PL','PT','RO','SE','SI','SK')RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'q_pieces_year-1' is not defined or its query items contain unresolved references.
As far as I know all is standard in the Framework regarding this data item. What am I missing that causes this error?
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 233
  • Forum Citizenship: +6/-3
    • Invisi - Vision on Information
Re: Filter gives error when using IN with multiple values
« Reply #1 on: 06 Oct 2017 04:06:15 am »
Who in the all-knowing and Always helpful Cognoise community knows which long toe of Cognos I stepped on and will share it with me?
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline New_Guy

  • Statesman
  • ******
  • Join Date: Mar 2016
  • Posts: 308
  • Forum Citizenship: +15/-0
Re: Filter gives error when using IN with multiple values
« Reply #2 on: 06 Oct 2017 08:07:44 am »
Hi,
Is the [Account Country Code] in the query subject 'q_pieces_year-1'? Try creating a filter with an 'or' condition and let us know if the issue still persists.
Good luck
New guy

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 233
  • Forum Citizenship: +6/-3
    • Invisi - Vision on Information
Re: Filter gives error when using IN with multiple values
« Reply #3 on: 10 Oct 2017 05:10:25 am »
First it was not, now it is. The thing is that the whole thing worked perfectly on another package with the same filter condition. Now that I update everything to a new filter, this starts to complain with the error. And it doesn't complain when I use only 1 value in the IN, or use a regular = instead.
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline Michael75

  • Statesman
  • ******
  • Join Date: Dec 2012
  • Posts: 323
  • Forum Citizenship: +20/-0
Re: Filter gives error when using IN with multiple values
« Reply #4 on: 18 Oct 2017 03:19:45 am »
I've often encountered this when working in a multi-language, or non-English environment (don't know if that's your case). The fix is to use semi-colon instead of comma as your separator. Try this:

Code: [Select]
[Account Country Code] in ('AT';'BE';'BG';'CZ';'CY';'DE';'DK';'EE';'ES';'FI';'FR';'GB';'GR';'HR';'HU';'IE';'IT';'LT';'LU';'LV';'MT';'NL';'PL';'PT';'RO';'SE';'SI';'SK')

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 233
  • Forum Citizenship: +6/-3
    • Invisi - Vision on Information
Re: Filter gives error when using IN with multiple values
« Reply #5 on: 18 Oct 2017 07:10:32 am »
You're right. I am surprised, because I tested this already, as I know this issue all too well. Also, I wonder how it worked previously, since I usually work in English to develop reports. Anyway, it's solved!

It still gives the unresolved issue when I use the short name of the data item from the query, but maybe that is because there is a condition in the query item?
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline Michael75

  • Statesman
  • ******
  • Join Date: Dec 2012
  • Posts: 323
  • Forum Citizenship: +20/-0
Re: Filter gives error when using IN with multiple values
« Reply #6 on: 19 Oct 2017 01:50:33 pm »
Quote
It still gives the unresolved issue when I use the short name of the data item from the query, but maybe that is because there is a condition in the query item?

Don't know. I'd like to help, but you'll have to give more details, such as the definition of this problematic data item.

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 233
  • Forum Citizenship: +6/-3
    • Invisi - Vision on Information
Re: Filter gives error when using IN with multiple values
« Reply #7 on: 09 Nov 2017 06:51:32 am »
Thanks for the offer. We now have an extra consultant here who experiences the same issue and is frying his brain over the source of the issue.
Few can be done on Cognos | RTFM for those who ask basic questions...

 


       
Twittear