Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Prompts with wild card

Status
Not open for further replies.
Jun 5, 2007
3
US
Hi All,

User would like to search for customer names that starts with 'A%' or 'WAN%' or 'AMERI%' etc. Is it possible to use in operator with like operator?
Normally, in a query we use
where ((CustName like 'A%') or (CustName like 'WAN%') or (CustName like 'AMERI%'))
How do we get this in a prompt? I can't use Search prompt because there are more than 50000 customers.
Please advise.
Thanks for your help!!
Swathi

 
If I am understanding correcylt then you need to try creating a filter on the prompt that reads

[CustName] starts with ('A') or [CustName] starts with ('AMERI') or [CustName] starts with ('WAN')

This should limit the search. I hope this helps.
 
Hi ymonge,
Thanks much for your response!!
It works if it is just A, WAN or AMERI but the user can input any string, so how can I get the user inputs and use them in the prompt with multiselect wild card string inputs
i.e the user can view the customer names that starts with the user inputs.
Please help!!
Thanks
Soujanya
 
How about adding one more prompt where you enter a value, e.g. "WAN". Then filter the second prompt with [CustName] starts with ?Parameter1?

 
Thank you globalbear for your response!!
The issue is, the user can input any number of search strings. Is there any javascript that can be used in this prompt that can collect the input string and concatenate it with '%'? I contacted cognos support but they said it cannot be done. Please help!!

Thank you
Swathi
 
I don't think I follow you now Swathi.

If you use 'starts with' you don't need to concatenate with '%'.

In SQL you would use something like
'select Custname from myTable where myTable.Custname like 'whatever%'

In Cognos you would filter on something like
'[CustName] starts with ?MyParameter'

Mark that there is no %-sign in the Cognos syntax.

This works fine for displaying all values that starts with e.g. 'WAN'. However, it seems that you want to filter on every value that starts with 'WAN' OR every value that starts with 'ABC' OR every value that... etc etc...

I don't know how to do that if that's what you want. My solution only filters on one (1) starts-with expression.
 
Hi Swathi,

You can use a text box prompt for this.
Create a text box prompt and modify the parameterized filter in your query as following:
CAST([data item], VARCHAR(XX)) LIKE ?Parameter1?

This enables the user for wild card search using '%'.
XX is the maximum number of characters of the data item in the database.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top