Hi All,
I am trying to do a simple list report with items from four query subjects. Lets call the four query subjects are Audit, Request, User, Delivery. Audit is inner joined with Request and User, Request is inner joined with Delivery and Delivery is left outer joined join with User. When i am pulling query items of the any two above subjects the sql generated is fine. But when i pull a query item from the remaining query subjects all the items are being used in the native sql query. And since all the items are being pulled out and subqueried later to get the required items the sql generated is very very long. I compared this native sql with sql generated by impromptu and it just had few lines with the right result. On the other hands this long query is giving me and oracle temp space error.
Please, can someone help me with this. Thanks!!
Hard to pinpoint this, cause this could be the result how your query subjects are defined. In any case, Cognos 8 has it's problems with generating efficient SQL in many cases.
Are these model query subjects or query subjects based on imported tables /views?
If model query subjects check out whether the sql generated is set to ' minimize sql ' or set to 'treat as view'.
Cognos 8 habit of creating more joins than strictly needed is the worst aspect of the tool..
There query subjects are model query subjects. I thought there was a problem with joins and when i played a little bit with joins and try to create the same list report as if i were doing a new query subject the query in framework manager is very small. But when i do it in Report Studio it seems to generate the same lengthy query. I am not able to comprehend how framework manager generates one sql and report studio generates a different one.
FM shows only the sql for 1 join between 2 subjects or the sql generated for a model query subject.
It cannot generate the SQL for a complete report which takes the whole model in consideration..