Keeping DB Schema name out of Report query?

[plarson3]

Using Crystal Reports 10 and Oracle:

The SQL queries generated by Crystal attach the DB schema name to the front of the table name (DBschema.TableName). Is there a way to prevent Crystal from doing that? Causes problems when delivering reports to be run under a different DB installation.

Oracle doesn't need the schema name to find the correct tables; it finds the schema based on the user ID.

 

[Turkbear]

Hi,
Do users only access tables in their own schema?

 

[plarson3]

Not necessarily. In my development world, yes. Once delivered to the customer/users, probably not; probably only one production schema.

Can't deliver reports/queries with some schemas attached that came out of a development database.

Hope that helps.

 

[Turkbear]

Hi,
The reason I asked was that without fully-qualifying the table name with its schema ( or using aptly named synonyms), how would reports handle tables of the same name in different schemas when the user running the report has access to both? ( Not a good design, but it happens)

 

[plarson3]

Thanks again. I am going to have to assume for now that my customer will not give the user access to multiple schemas. In the first delivery actually, the reports will be run automatically by our system, as opposed to be run on demand by the users.

So the question remains, can the schemas be eliminated from the queries in Crystal?

 

[plarson3]

I have learned that if you write your own SQL query using the Command function under Database Expert, you have complete control over the query, and therefore can leave out or remove the table schema.

To me, that is a workaround, and will require me to redo a number of reports.

I am still curious if there is a way to keep the DB schema out of the query generated when using the standard Crystal table/field selection process.

Any thoughts greatly appreciated.