Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Issues with Unions in reports in 11.2.1

    Posted Thu February 10, 2022 08:21 AM
    ​Good morning!

    We recently upgraded a customer from Cognos 11.0.13 to Cognos 11.2.1 IF3.  Windows environment.  New server - so not an upgrade in place.  After install and config we imported a deployment archive from production 11.0 system into new content store database.

    In testing after the upgrade we ran into an issue with a report -- I wonder if anyone else has seen this.

    Report runs without issue in 11.0.
    It is using a DQM package against Oracle 12.

    The query structure in the report is as follows
    - Query1 from Package
    - Query2 from Package
    - Query3 joins Query1 and Query2
    - Query4 from Package
    - Query5 from Package
    - Query6 joins Query4 and Query5
    - Query7 Unions Query3 and Query6

    All Queries 1 thru 6 test correctly (e.g. you can view tabular data without issue).

    Query7 throws a java error "XQE-GEN-0002 An unexpected exception occurred: java.lang.NullPointerException essentially immediately, both when used in Tabular Data and when used on the report page.  Nothing useful in the logs that we've found.

    In troubleshooting we've done the following:

    1.  We generated the SQL for Query7 and pasted it into SQLPlus.  It runs without error.
    2.  We changed the Union operator to an Except operator -- no problem... the query runs.  So the problem appears to be with the union
    3.  We changed the Union properties from Preserve Duplicates to Remove Duplicates, no change.
    4.  We updated the Oracle JAR to the Java8 version (and removed the Java7 jar from the Drivers folder).  No change.

    Today we plan to republish the package using CQM to see if that changes the outcome  (our thought is to either prove or disprove that it is a DQM-only issue).

    Has anyone else experienced anything similar upon upgrading to 11.2.1, and if so, how did you resolve it?

    Thank you in advance for your ideas and thoughts!

    Rich

    ------------------------------
    Richard Chester
    Director of Business Intelligence
    LPA Software Solutions
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Issues with Unions in reports in 11.2.1

    Posted Fri April 21, 2023 10:53 AM

    Hi there Richard! Not sure if you resolved this, but my team saw this error as well and what solved it for us was going to the unioned query thats giving the error and change the properties of the union to preserve duplicate rows instead of removing them. Not sure why we needed to do this if it was working previously for my team but that seemed to fix it. Just click on the union itself in the Queries screen and on the right hand side click on the duplicates option. Hope this helps! 



    ------------------------------
    Joel Zorrilla
    ------------------------------

    Original Message


  • 3.  RE: Issues with Unions in reports in 11.2.1

    Posted Fri April 21, 2023 11:19 AM

    you may already know this, but a union can have more than two queries. You dont need to nest unions. You could union all of the four package queries together with a single union.  
    This would help to simplify the problem for troubleshooting.


    Replace the nested unions with a single union. 
    If the problem persists then I would create a new union query that unions Q1,2,4,5.
    That should simply select columns from the union.
    See if that works.
    if that works then you know its an issue with your union top query - start removing columns from that and retest.

    If the new union fails then start removing columns from the lower queries and retest to find the column with an issue.



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


Related Content