Hi everyone
We are running 11.1.3 (upgrading soon). Our problem is occurring with a report with a filter that contains a not. Our datasource is an Oracle relational database and the package is a DQM package.
not ( [query item] in ([otherquery].[queryitem] )
This is reporting an oracle ORA-00920 Invalid Relational Operator. The [otherquery] has at most two rows and the item we are comparing is a year (yyyy). Interestingly, an earlier version of this report is working correctly with a not in the filter.
Has anyone had this type of problem before, or have any comments or suggestions? I managed to get it to work by creating a [new query item] in the first query with the filter that basically has an
if [query item] in ( [otherquery].[queryitem]) )
then ('current')
else ('history')
and then added a filter that was
[new query item] = 'history'
I am baffled by this and not sure why I am having problems. Any suggestions or help welcome.
Thanks in advance'
Quote from: Penny on 23 Mar 2021 11:22:11 AM
Hi everyone
We are running 11.1.3 (upgrading soon). Our problem is occurring with a report with a filter that contains a not. Our datasource is an Oracle relational database and the package is a DQM package.
not ( [query item] in ([otherquery].[queryitem] )
This is reporting an oracle ORA-00920 Invalid Relational Operator. The [otherquery] has at most two rows and the item we are comparing is a year (yyyy). Interestingly, an earlier version of this report is working correctly with a not in the filter.
Has anyone had this type of problem before, or have any comments or suggestions? I managed to get it to work by creating a [new query item] in the first query with the filter that basically has an
if [query item] in ( [otherquery].[queryitem]) )
then ('current')
else ('history')
and then added a filter that was
[new query item] = 'history'
I am baffled by this and not sure why I am having problems. Any suggestions or help welcome.
Thanks in advance'
Hi,
Do you see the same issue if you change the expression to be [query item] not in ([otherquery].[queryitem])
The original expression you posted seems to be missing a closing parenthesis also, but I'm assuming this is a typo in the post? You'd get syntax errors otherwise.
Cheers!
MF.
Thank you for your response. The issue has been resolved.
It started with a model change. We published a test package and built the report against it. When confirmed we republished the 'base' package. When I opened the report both packages were showing (not sure how that happened). I replace the test package with the 'base' package, but then had a problem where some queries couldn't 'see' the other queries that I needed to filter by. I recreated the report from scratch and in doing so made an error in another filter (same query I was having trouble with). The filter I made an error on was to use an in ([otherquery].[item]) but the mistake was the query name (it was trying to filter based on itself). This generated the invalid relational operator ...the sql was crazy. I corrected it and the NOT filter then worked as expected.
Quote from: Penny on 24 Mar 2021 02:52:41 PM
Thank you for your response. The issue has been resolved.
It started with a model change. We published a test package and built the report against it. When confirmed we republished the 'base' package. When I opened the report both packages were showing (not sure how that happened). I replace the test package with the 'base' package, but then had a problem where some queries couldn't 'see' the other queries that I needed to filter by. I recreated the report from scratch and in doing so made an error in another filter (same query I was having trouble with). The filter I made an error on was to use an in ([otherquery].[item]) but the mistake was the query name (it was trying to filter based on itself). This generated the invalid relational operator ...the sql was crazy. I corrected it and the NOT filter then worked as expected.
Wow! Huge credit to you for managing to figure that out. Really glad you got it resolved!
MF.