in function

[limkw]

I am trying to use the filter the field "Year" to certain range rather than equal to particular year. I try to change the "=" to "in" and COGNOs will returned with error. Need advise why is that so, isn't in function doesn't apply to varchar datatype? Need some workaround solutions.

[ODRPTDB].[COZ1S0T0].[Year] = cast(?Parameter3?, varchar(8))

No errors.

[ODRPTDB].[COZ1S0T0].[Year] in cast(?Parameter3?, varchar(8))

QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 36 of: "[ODRPTDB].[COZ1S0T0].[Year] in cast"
QE-DEF-0261 QFWP - Parsing text: [ODRPTDB].[COZ1S0T0].[Year] = cast(?Parameter3?, varchar(8))

[MFGF]

I am trying to use the filter the field "Year" to certain range rather than equal to particular year. I try to change the "=" to "in" and COGNOs will returned with error. Need advise why is that so, isn't in function doesn't apply to varchar datatype? Need some workaround solutions.

[ODRPTDB].[COZ1S0T0].[Year] = cast(?Parameter3?, varchar(8))

No errors.

[ODRPTDB].[COZ1S0T0].[Year] in cast(?Parameter3?, varchar(8))

QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 36 of: "[ODRPTDB].[COZ1S0T0].[Year] in cast"
QE-DEF-0261 QFWP - Parsing text: [ODRPTDB].[COZ1S0T0].[Year] = cast(?Parameter3?, varchar(8))

Why are you casting your parameter to a varchar? Is Year a string or is it a number?

When you use an "in" operator, the list of values needs to be included in brackets

eg [Year] in (2011, 2012)

What sort of prompt are you using? Why not just use a filter that uses the expression

[ODRPTDB].[COZ1S0T0].[Year] in (?Parameter3?)

MF.

[limkw]

MF,

Thanks for your advice!

[Year] datatype is decimal.

I change the statement to

[ODRPTDB].[COZ1S0T0].[Year] in (cast(?Parameter3?,varchar(8)))

and there are no errors. The prompt is able to select 2013, 2014 but the strange output is it shown beside other years as well. Can you tell me what I need to do on the properties on the value prompt to sync what I have input.

[Francis aka khayman]

where to begin...


1.) how'd you know the properties of the value prompt will fix this?

Can you tell me what I need to do on the properties on the value prompt to sync what I have input.

2.) if you are trying to return a range of years, it would be better to use YEAR in integer datatype and use a greater than and less than filter i.e  YEAR > ?start_year? and YEAR < ?end_year?

3.) you usually use IN if you have a set of values to return. it can return range with some tweaking but the easier way to get a range of values is to use what i said in #2

4.) it will be easier if you let the people who respond guide you. you posted in the forum because you can't get the report to work in the first place.