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:
Sent: Thu July 23, 2020 12:05 AM
From: HENK CAZEMIER
Subject: Strange Generated Query Results
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:
Sent: Wed July 22, 2020 02:46 PM
From: jerome roberts
Subject: Strange Generated Query Results
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:
Sent: 7/22/2020 1:06:00 PM
From: HENK CAZEMIER
Subject: RE: Strange Generated Query Results
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
Original Message:
Sent: Wed July 22, 2020 11:43 AM
From: jerome roberts
Subject: Strange Generated Query Results
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
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