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
In querry studio
1
Create a new filter in FM with the expression:
[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.
Quote from: PaulM on 15 May 2011 05:08:24 AM
Create a new filter in FM with the expression:
[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! :)
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
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 [NS].[TABLE].[FIELD] like #sq(prompt('search string','token','','%','','%'))# with
{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
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
What error are you getting? What database are you using?
In the meantime there are other possibilities. Maybe something like
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.