Cartesian joins are joins that are the “product” of two sets. A popular join used in Report Studio is the UNION join. This will take the result of one set – the results of, for example, a SELECT statement that yields 5 columns – and “merge” it with the result of a second select statement that has the same number of columns of the same type. Each query might return 10 rows, but the result of the UNION join between them will result in 20 rows (Other Cartesian joins are the EXCEPT, which returns rows that are only in one of the source queries or the other, but not both, and the INTERSECTION, which returns elements that are in both source queries).
Cartesian joins are useful when you need to present several different results within the context of a single query – you can create 3 different queries, and then UNION, INTERSECT or EXCEPT them together and present them in a single List object. As long as they have the same number of columns this should work fine.
In the example below, Query5 is the result of an INTERESCT between Query3 and Query4 – it will select the elements that are common between them.
Cognos 8.3 doesn’t always behave as expected however, and a Report Studio report that that uses UNION joins may result in an unexpected error:
ORA-32035: unreferenced query name define in WITH clause
This can be maddening because the report may work correctly when rendered in HTML, but generate the error when the user requests a PDF.
The source of the error is an inappropriately formed query generated by Cognos, and it can be avoided by restricting the query that is the result of the Cartesian Join from using the WITH clause. The WITH clause allows the database server to perform calculations on sub-queries more efficiently. However, in this case Cognos may not manage the formation of the clause correctly, and using it should be avoided to avoid this error. This setting is found under the Properties of the query, in the setting “Use SQL With Clause”:
Setting this to “No” should allow your report to run correctly.
#1 by Andrew McComas on October 11, 2012 - 1:26 pm
Thank you for this posting. I was fighting a UNION query in Cognos 10.1.1 and setting the Use SQL With Clause to No allowed it to return the data I was expecting.
Again, thank you!