Getting the data from other schema in the same database

[Former Member]

Hi,

I have a report that has few subreports. Some times some of these sub reports retreives the data from the same table in different schema.

ex: if I have table TEST on both schema A and B, and I use A.TEST in my report, but it get the data from B.TEST when I had login as user A.

Do anybody have any idea what is going on here. I use CR XI and Oracle 10g.

Thanks and appreciate any help.

[former_member11696]

Hello,

Schemas are like distinct connections so you need to log into both A and B and then link them.

You may want to upgrade for free to CR XI R2 also, use your XI keycode:

https://smpdl.sap-ag.de/~sapidp/012002523100011802732008E/crxir2_sp4_full_build.exe

https://smpdl.sap-ag.de/~sapidp/012002523100013876392008E/crxir2win_sp5.exe

https://smpdl.sap-ag.de/~sapidp/012002523100015859952009E/crxir2win_sp6.exe

Thank you

Don

[Former Member]

Hi Don,

Thanks for the reply.

Sorry if I was not clear with my question, I do not want to get the data from schema B, I want the data from schema A. I am login to schema A. But the report pulls data from the same table in schema B.

Thanks

[former_member11696]

Ah... Try CR XI R2, there were a few fixes for Oracle.

Or change the Properties of the connection and fully qualify the connection if it's not already.

Thank you

Don

[Former Member]

Thanks again Don.

Yes we are using CR XI R2 and always set the properties of the connection to set the correct schema. It works 100% in the CR developer.

This happens when we use CR viewer.

Stange issue here is it change the fully qualify name only in some tables in the same query and fully qualifier on other tables stays unchanged.

For ex if we have TEST, TEST1, TEST2 tables in both A and B schema:

In CR Developer my query work as expected like:

select TEST.col1, TEST.col2, TEST1.col1, TEST2.COL1 from

A.TEST, A.TEST1, A.TEST2 where .......

Will change to:

select TEST.col1, TEST.col2, TEST1.col1, TEST2.COL1 from

A.TEST, B.TEST1, A.TEST2 where .......

Appreciate your help.

Thanks

[former_member11696]

Ah, that makes a difference.

What DEV tool and Report engine are you using?

And how are you connecting/setting the location info?

Code snippet would be good to see...

Don

Edited by: Don Williams on May 16, 2011 1:09 PM