Dynamic database name in ODBC query ?

[sdonaldson]

Using CR 8.5 Developer Edition on Windows 2000 to query an MS-SQL 7.0 database via ODBC (p2sodbc.dll).
In order to obtain the desired results from a report I have had to edit the WHERE clause of the generated SQL SELECT statement. The report works fine, but only for one database.
Our data is organised such that each "company" has its own database in the SQL Server, so I need to be able to change the prefix of the table names accordingly, for example, replacing "PM001.PM.Supplier" with "PM002.PM.Supplier".
I suspect from reading other posts that this cannot be done.
If my suspiscion is correct can anyone suggest a way to replicate the following SELECT statement using the CR tools ?

SELECT
PLitems.suppr_code, PLitems.ref, PLitems.type,
PLitems.total, Supplier.name
FROM
{ oj PM001.PM.PLitems PLitems
LEFT OUTER JOIN
PM001.PM.Supplier Supplier
ON PLitems.suppr_code = Supplier.suppr_code}
WHERE
PLitems.total > 0 AND
(SELECT
sum(sign(X.total))
FROM
PM001.PM.PLitems X
WHERE
X.suppr_code = PLitems.suppr_code AND
abs(X.total) = abs(PLitems.total)) = 0
ORDER BY
PLitems.suppr_code ASC,
PLitems.total ASC

The aim here is to select transactions from the PLitems table where there is an equal number of debits and credits that could be matched against one another for each Supplier.

If I switch to the SQL driver "p2ssql.dll" the "PM001" prefix is dropped from the table names and the report works for each "company" that I select (in my application).

FYI I cannot make any changes to the SQL Server (so no Stored Procedures, then).

Thanks in anticipation,
Shaun

 

[sdonaldson]

Sorry, I should add the following information...

1. The "PM001" prefix has been set by the System Administrator as part of the ODBC driver configuration as most installations of our application will have at least company number 001. This cannot be changed.

2. I removed the "PM001.PM" prefix that appears in the Table input on the Set Location dialog but this appeared to have no effect at all.

It's as if CR is aware that the query has been (partially) hand-coded so instead of using placeholders for the table names which it replaces at runtime it uses the table names specified in the FROM clause verbatim. ???

I look forward to your suggestions.
Shaun