Cognos Analytics

Cognos Analytics

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


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Strange Generated Query Results

    Posted 07/22/20 11:43 AM

    I have a query with a filter using an equal comparison operator (=) on a non-indexed database column.  When I look at the generated SQL in 'Native SQL' mode, the where '=' clause is missing.  When I view the generated SQL in 'IBM Cognos SQL' mode, the where '=' clause is there.  Anyone know what's causing this behavior?

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.

    #CognosAnalyticswithWatson


  • 2.  RE: Strange Generated Query Results

    Posted 07/22/20 01:06 PM

    Hi Jerome,
    What database technology are you building this on? Are the tables in the same data source ? If the tables are in different sources, then the join is performed in the Cognos software and would not show up in Native SQL.

    If you want we can do a webex and I can have a look with you.
    PM me at henk.cazemier@ca.ibm.com
    // Henk



    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: Strange Generated Query Results

    Posted 07/22/20 02:46 PM

    Hi Henk,

    I made one mistake in my post.  The table column referenced in the comparison is indexed.  But it changes nothing.

    The answers to your questions.

    What database technology are you building this on?

    Sybase ASE 16

    Are the tables in the same data source ?

    YES

    If the tables are in different sources, then the join is performed in the Cognos software and would not show up in Native SQL.

    NO JOIN.

     

    Here are some screen shots.  

    The filter expression with the – comparison.

     

    Notice the Native vs IBM generated SQL.

    IBM Cognos SQL version has correct 'where' condition, Native version is incorrect, although that is the default version.

     




    Original Message


  • 4.  RE: Strange Generated Query Results

    Posted 07/23/20 12:05 AM

    Hi Jerome,
    Ah, its a filter expression in Reporting that you're dealing with.
    The filter doesn't get pushed to the Native SQL if one of the terms is not supported.
    Is the query subject [Customer Info] in the FM model defined on PassThroughSQL ?
    What is the technical datatype of the field? I suspect it is a CHAR(10) field.
    Cognos adds the trim and cast functions. One of those is not supported by the underlying data provider (at least that is what Cognos thinks).
    What happens when you change the expression to:
    rtrim ( cast ( ffff, varchar(10) ) ) = '1234'  does that show up in the native sql ?
    // Henk



    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 5.  RE: Strange Generated Query Results

    Posted 07/24/20 08:09 AM
      |   view attached
    Hi, 

    If you are looking at Generated SQL of the query, you will not see the where clauses by default. You can change the Use SQL Parameters property in query properties that can show the where clauses as well as parameterized variables. 

    Regards

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------

    Original Message


  • 6.  RE: Strange Generated Query Results

    Posted 07/24/20 12:36 PM
    What is the technical datatype of the field? I suspect it is a CHAR(10) field -> CHAR(16)...I cannot get to the FM definition, but here is the lineage description

    I tried the cast and trim code, but it did not make a difference.  Also, modifying the use sql parameters made no difference. 


    ------------------------------
    jerome roberts
    ------------------------------

    Original Message


Related Content