Author Topic: Search for values filter for large strings  (Read 7025 times)

Offline SynexusStuart

  • Community Leader
  • *****
  • Join Date: Jul 2009
  • Posts: 101
  • Forum Citizenship: +0/-0
Search for values filter for large strings
« on: 11 May 2011 09:11:49 am »
Hi,

We have a data item in a package which contains long strings of text (up to 2048 characters). These strings of text contain medical information about individuals and we need this text to be searchable to find specific strings of text (not necessarily separated by spaces).

Example: I might want to find all records who have the text string "Osteo" in this data item.

It is easy to do this in Report Studio but we need to ba able to do it in Query Studio. Can anyone think of any way to do this?

Help would be appreciated
Stuart

Offline jive

  • Community Leader
  • *****
  • Join Date: Jul 2009
  • Posts: 106
  • Forum Citizenship: +0/-0
Re: Search for values filter for large strings
« Reply #1 on: 13 May 2011 12:49:36 pm »
In querry studio
1

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,632
  • Forum Citizenship: +250/-1
    • Paul's Cognos Blog
Re: Search for values filter for large strings
« Reply #2 on: 15 May 2011 05:08:24 am »
Create a new filter in FM with the expression:

Code: [Select]
[NS].[TABLE].[FIELD]  like #sq(prompt('search string','token','','%','','%'))#
The users will need to drag that filter directly into the query and it will prompt the user.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: Search for values filter for large strings
« Reply #3 on: 19 May 2011 03:33:24 am »
Create a new filter in FM with the expression:

Code: [Select]
[NS].[TABLE].[FIELD]  like #sq(prompt('search string','token','','%','','%'))#
The users will need to drag that filter directly into the query and it will prompt the user.

Another top tip from Paul.  I never ceased to be impressed by his inventiveness! :)
Meep!

Offline SynexusStuart

  • Community Leader
  • *****
  • Join Date: Jul 2009
  • Posts: 101
  • Forum Citizenship: +0/-0
Re: Search for values filter for large strings
« Reply #4 on: 25 May 2011 01:39:41 am »
Thanks Paul, it does work like that but users are finding the solution a bit unstable.

We are getting errors when they try and bring certain other Data Items into the report, for instance:

They may drag in Country, Region and the prompted data item, but when they bring across a measure, an error occurs. also, sometimes when filtering the report errors crop up.

As a rule it is not an unstable package but when this new data item is used, we are finding that it is very difficult to get to the end of a report without an error.

The most common error is this: UDA-SQL-0358 Line 7: Syntax error near "like".

Any thoughts?

Stuart

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,632
  • Forum Citizenship: +250/-1
    • Paul's Cognos Blog
Re: Search for values filter for large strings
« Reply #5 on: 01 Jun 2011 02:06:50 am »
That's very odd. Does it continue to happen if you remove and re-add the filter? Do you have any crossjoins or subqueries in the SQL?

What happens if you replace
Code: [Select]
[NS].[TABLE].[FIELD]  like #sq(prompt('search string','token','','%','','%'))# with
Code: [Select]
{table.field like #sq(prompt('search string','token','','%','','%'))#} The curly brackets will tell Cognos to send the snippet directly to the database without trying to parse it.

** After researching this it looks like Cognos is expecting Cognos SQL instead of db SQL. Try the bit with the curly brackets, and if that doesn't work we'll find another way.

See here: https://www-304.ibm.com/support/docview.wss?uid=swg21339703

Offline SynexusStuart

  • Community Leader
  • *****
  • Join Date: Jul 2009
  • Posts: 101
  • Forum Citizenship: +0/-0
Re: Search for values filter for large strings
« Reply #6 on: 15 Jun 2012 07:42:55 am »
Paul,

I realise that it's been a while since I raised it but I never got a resolution to the issue (even trying the {} method above). We have been asked for this solution again recently and I am still unable to get it working in a stable way.

Do you (or anyone else) have any more ideas?

Stuart

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,632
  • Forum Citizenship: +250/-1
    • Paul's Cognos Blog
Re: Search for values filter for large strings
« Reply #7 on: 17 Jun 2012 09:32:38 am »
What error are you getting? What database are you using?

In the meantime there are other possibilities. Maybe something like
Code: [Select]
position([NS].[TABLE].[FIELD],#prompt('search string','string')#)>0Since "like" filters with a beginning wildcard can't use indexes, this alternate solution doesn't make me feel too bad.

It's worth mentioning that I have a filter exactly like the one I described last year in production on an SQL Server database.

 


       
Twittear