Search for values filter for large strings

[SynexusStuart]

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

[jive]

In querry studio
1

[CognosPaul]

Create a new filter in FM with the expression:

Code Select Expand

[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.

[MFGF]

Create a new filter in FM with the expression:

Code Select Expand

[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! :)

[SynexusStuart]

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

[CognosPaul]

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 Expand

[NS].[TABLE].[FIELD]  like #sq(prompt('search string','token','','%','','%'))# with

Code Select Expand

{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

[SynexusStuart]

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

[CognosPaul]

What error are you getting? What database are you using?

In the meantime there are other possibilities. Maybe something like

Code Select Expand

position([NS].[TABLE].[FIELD],#prompt('search string','string')#)>0
Since "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.