Hi,
We have Oracle DB and we have done join of two query subject at the report level. I generated the SQL from the query subject which came out after the join but when i am running the generated SQL in oracle DB. I am getting error.Could anyone please share the experience on this?
Thanks,
Sunny
Quote from: sunny bachan prasad on 23 Mar 2018 04:49:57 AM
Hi,
We have Oracle DB and we have done join of two query subject at the report level. I generated the SQL from the query subject which came out after the join but when i am running the generated SQL in oracle DB. I am getting error.Could anyone please share the experience on this?
Thanks,
Sunny
Hi,
"getting error"? That's not much for anyone to go on, unfortunately. I can't see how anyone can help unless you provide details on the query and the error...
MF.
Hi MF,
Let me put my question in another way. How we can run the Query in DB which i generated from the Query subject which is the result of join between two query subject. Usually we are able to run the Query generated from Cognos in DB directly but when SQl is from the query subject which is the result of join between QS in cognos i am not able to do it.
Quote from: sunny bachan prasad on 23 Mar 2018 05:11:13 AM
Hi MF,
Let me put my question in another way. How we can run the Query in DB which i generated from the Query subject which is the result of join between two query subject. Usually we are able to run the Query generated from Cognos in DB directly but when SQl is from the query subject which is the result of join between QS in cognos i am not able to do it.
I assume the join is being done locally on the Cognos server? Are you seeing two native SQL queries and one Cognos SQL query?
You haven't given us even the most basic description of what you're seeing, so it's virtually impossible to suggest ways to help.
MF.
Hi MF,
Sorry that i am not able to put my question in good way. I have tried once again with example and lets see if it work
Example:
Suppose i have a list report in Report studio which is using the query subject QS3. Now this QS3 is the outcome of local inner join between QS1 and QS2. If we will generate the SQL from QS1 and QS2 seperately and run it in Database then we can see the data for these QS separately . But when i was trying to run the SQl from QS3 in DB i cannot able to run it since i didn't able to find the join in SQL which we defined locally in cognos.
Quote from: sunny bachan prasad on 23 Mar 2018 08:04:46 AM
Hi MF,
Sorry that i am not able to put my question in good way. I have tried once again with example and lets see if it work
Example:
Suppose i have a list report in Report studio which is using the query subject QS3. Now this QS3 is the outcome of local inner join between QS1 and QS2. If we will generate the SQL from QS1 and QS2 seperately and run it in Database then we can see the data for these QS separately . But when i was trying to run the SQl from QS3 in DB i cannot able to run it since i didn't able to find the join in SQL which we defined locally in cognos.
From what you're describing it sounds to me like the join is being performed locally on the Cognos server. The report is sending two queries to the database, returning the result sets from each, and then joining the result sets together locally. The bottom line is that if you're looking to see what single query is being sent to the database to do all of this, you're looking for something that simply does not exist.
Cheers!
MF.
Sent from my iPhone using Tapatalk
Hi MF,
Thanks for your time. Finally you got me what i was trying to ask.:)
Is there is any way to get the single SQL when join has been locally done. Since it is easy to run that SQL in DB and test the data. I tried to change the type of SQL from Native to cognos but that also didnot work.