Invalid Relational Operator for relational report filter

[Penny]

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'


     

[MFGF]

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.

[Penny]

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.

[MFGF]

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.