SOLVED: Constructing an IN clause

[pr0ph3t]

Does anyone know how to construct and IN clause for a query filter based on a comma separated list typed into a text box prompt? For example, I have a text box prompt that provides a parameter called IDFilter. I want the user to be able to type in:

Code Select Expand


one,two,three

into the text box prompt and I then want to do the equivalent of:

Code Select Expand


select * from names where id in ('one','two','three')

using a filter in a query like:

Code Select Expand

[ID] in ?IDFilter?

[blom0344]

There is no need since Cognos offers a multivalue prompt from either a prompt query or a prompt storing static values. All the user has to do is to select from the presented list offered..

[Lynn]

If a text box prompt is really necessary for some reason then you can set it to muti-select. They would enter each value one at a time and then click the insert button to move each to the choices box. Presenting them with choices in list box might be easier as blom suggests.

[pr0ph3t]

Thanks, all. Lynn's solution was what I needed in this case. I do still run into situations where it'd be extremely convenient to paste in a large list of CSVs and have it used in an IN clause. I'll figure it out some day.

[CognosPaul]

If you absolutely must have CSVs, you could try the following:

Code Select Expand

[Namespace].[Table].[Field] in (#csv(split(',',prompt('CSV Prompt','token')))#)

So if you write in a,b,c it will be converted into:

Code Select Expand

[Namespace].[Table].[Field] in ('a','b','c')

If you need to filter on numeric values, just get rid of the csv and split functions.

[tjohnson3050]

You can also setup a text box to accept multiple values pasted in by setting the 'Multi Line' property.  This makes it easy to copy a list of values out of excel and paste into a prompt.