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))
Quote from: limkw on 13 Nov 2014 02:35:56 AM
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.
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.
where to begin...
1.) how'd you know the properties of the value prompt will fix this?
Quote from: limkw on 17 Nov 2014 03:39:01 AM
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.