Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Oracle Invalid Relational Operator When Using NOT in Filter of Report

    Posted Tue March 23, 2021 07:41 PM
    We are running Cognos Analytics 11.1.3 and I have a report that for some reason reports an Oracle error 00920 Invalid Relational Operator in a query filter.
    The filter is checking to make sure a query item does NOT exist in another query.  Our datasource is an Oracle database and we are using a DQM package built against a relational database.

    not ( [item] in ( [otherquery].[queryitem]  )

    Strangely enough I have used this techniques in other reports and am a little concerned that this would report an Oracle error.   The [otherquery] has at most two rows of data and is quite a simple query.  An early version of this report still operates with the not filter and I am at a loss as to why this is occurring.

    If anyone has experienced this or has any advice it would be greatly appreciated.

    ------------------------------
    Penny Flower
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Oracle Invalid Relational Operator When Using NOT in Filter of Report

    Posted Tue March 23, 2021 07:59 PM
    Hi
    NOT IN is a valid relational operator , I  don't think you can use NOT as is.Also you are missing one closing parenthesis.
    Can you send the full expression if possible
    Thank you

    ------------------------------
    Sanjay Chakravarty
    ------------------------------

    Original Message


  • 3.  RE: Oracle Invalid Relational Operator When Using NOT in Filter of Report

    Posted Wed March 24, 2021 08:43 AM
    Good Morning,

    Your list [otherquery].[queryitem] must always result in a distinct and uniform list such as:
    ('dog', 'cat', 'fish')
    There the filter would result as:
    not ( 'goat' in ('dog', 'cat', 'fish'))
    not ( 'dog' in ('dog', 'cat', 'fish'))
    not ( 'cow' in ('dog', 'cat', 'fish'))
    not ( 'horse' in ('dog', 'cat', 'fish'))

    Your syntax is trying to produce something like:
    not ( 'goat' in ('dog'))
    not ( 'dog' in ('cat'))
    not ( 'cow' in ('fish'))
    ...which does not work technically or logically.

    I have never tried to embed an external query within a filter, but I guess it is worth a try.  Something like:
    not ( [item] in ([Query Pets]))
    ...with the not ( [item] in ([Query Pets])) filter being used in the current query Query1, not in [Query Pets] which contains a single field of pets or something like:
    not ( [item] in ([select animal_type from Query Pets]))

    I have never tried to embed the query.  It's just a thought.

    Warm Regards,
    Eli



    Original Message


  • 4.  RE: Oracle Invalid Relational Operator When Using NOT in Filter of Report

    Posted Wed March 24, 2021 10:19 AM
    Thank you for the responses.  The syntax error in my example was just a typo in the message.

    In the end, it was another filter causing the problem.  It is a long story but I published a test package with minor model changes, and used it for this report during development in our DEV environment, then republished my usual package.  The report then had two packages which was not my intention. I replaced the test package and the report looked okay (only one package), but a strange thing happened in the queries, and the main query could not 'see' the other queries in the report.  Therefore I rebuilt the report, but in doing so I introduced an error in a filter in querymain.    It read something like  [key] in ([querymain].[key] - I had mistakenly entered the querymain in the filter rather than a 'different query' as intended.  This caused very ugly sql and also the relational operator error.  Once I fixed that filter, the filter with the NOT worked as expected.

    Thank-you again.

    ------------------------------
    Penny Flower
    ------------------------------

    Original Message