0

I have a report where a query named "Query1" is created using the default data items present in the package. When I look into the Tools ---> Show Generated SQL/MDX, IBM Cognos shows 2 generated Native SQL queries for the query named "Query1". How does 2 query get created --- Query1.0 & Query 1.1 ? Under what scenario does this happen? FYI --- The changes that is done in the environment is that a new model is added under a product family at the starting of this month. Could this be the reason why 2 SQL queries has been generated? Can someone explain what happens at the backend?

user3215886
  • 1
  • 1

1 Answers1

0

Generally, there are two reasons that you will see multiple separate queries in the generated SQL:

  1. Some or all of the objects referenced in the Cognos query don't have defined relationships to each other in the model/package
  2. The objects referenced in the Cognos query come from two or more different data sources

In the case of 1 you will end up with a cross join. In the case of 2, Cognos will retrieve the data separately from each source and then stitch them together in memory on the Cognos report server.

There may be other reasons that more than one separate query is shown in generated SQL but these two are the ones I've come across in my work.

Johnsonium
  • 2,005
  • 1
  • 13
  • 15
  • Consider the case 2. In my environment, a data item named "data Item 1" is getting the data from one model of storage array say "Model A" till March 15, for instance. Later on march 16, a new model of a storage array say "Model B" is configured to be added in the same cluster so the new model becomes "ModelAModelB". Also a new member is created in the members list. When I create a chart that displays monthly data, I want to see the Model A's data points until March 15 and from March 16, I want to see the combined data in a single trend line. how do I do this? – user3215886 May 25 '17 at 09:14
  • That's a different question, but it sounds like you need a union. A union is appropriate when you have a common set of columns and want to merge the two sets together into one set, retaining the original column makeup. This only works, however, if the columns match exactly in order, number and data type. – Johnsonium May 25 '17 at 18:33