Submitted SQL Different from Query Spec

[c8guru]

Has anyone come across an issue where the submitted SQL is different from the SQL generated in Report Studio?  We have a report that is using a join to a view that is providing the last DW load date.  The join field is included in the Query spec but not in the report.  When generating the SQL in Report Studio it is correctly including the join and produces the correct result when copying and pasting into a query tool and running against the database directly.  However, when running in Cognos the join is not submitted to the database, which has been verified via the dba views in Oracle.

It appears that C8 is not submitting the Query from the spec but only what is included on the report. 

[goose]

That is correct, if you use data items from different query subjects in your package in the report, even if the data item is in the query subject, if it is not used on the report then it will not get generated in sql. In your case this means you will not join to your view.

There are two possible solutions to this:

1. You can specify this data item as a property of your list/crosstab/etc via properties attribute in RS and then it will force the data item to be used in the sql and hence your join will be there

2. You must combine your data items, including the column from the view, into one query subject in FM

I think option 1 is more flexible but both will work. To see sql that really gets sent to the db you must do this:

- Validate the report via Tools -> Validate with options
- Select information as the validation level
- look for a UDA-QOS-0004 Native SQL being executed message - this is the sql getting sent to the db

Hope you come right

[c8guru]

Thanks Angus for your reply.  The reason why this is so perplexing is that the query item in question is coming from a model query subject that has all the columns merged into it.  The query item is even included in the Query specification for the report in RS even though it is not included as a colum in the list object.

[goose]

that is strange, check if you test the model query subject in FM if the view is used. What type of join is defined between the query subject(s) and the view in FM, maybe its an outer join and cognos wont use it then.

[goose]

After doing some tests this is what I found. Cognos will optimize the sql thats gets sent to the db to avoid unnecessary joins when it can. I will explain with an example:

- Created a new model and imported two tables (calendar dimension, fact) to create two datasource query subjects.
- The calendar query subject has two columns fin_year and fin_week that uniquely identify it (I know its not perfect but thats what our dwh architect decided).
- The fact query subject also has fin_year and fin_week etc on it.
- Created a 1<->n relationship between the calendar query subject and the fact query subject on fin_year and fin_week.
- Then I created a model query subject that had fin_year and fin_week off the calendar query subject and sales off the fact query subject.
- If I test the model query subject the sql generated does not join to the calendar table since fin_year and fin_week uniquely identify a calendar record and they are on the fact table. Only when I included other columns from the calendar table would the sql generated join to the table

This makes sense from a performance perspective, check that you are not in this same scenario maybe

[blom0344]

Angus, what version are you using? This is one of the main things in 8.1 that we reported to Cognos as a bug.
In our experience Cognos DOES create the join, even if you query only objects from one table...
We have not seen a solution in the fix list of version 8.2

[goose]

I was testing on 8.1.2 MR2 with an informix datasource.