Hi,
We want to do a query in different datasource (e.g. ds.1, ds.2, ds3).
Therefore, I prepare a query to select the datasource no (i.e. 1, 2, 3).
When I try query using following:
<cfquery name="GetDS1Result" datasource="#ds.1#">
SELECT name, grade
FROM employee
</cfquery>
<cfquery name="GetDS2Result" datasource="#ds.2#">
SELECT name, grade
FROM employee
</cfquery>
<cfquery name="GetDS3Result" datasource="#ds.3#">
SELECT name, grade
FROM employee
</cfquery>
<cfquery name="GetResult" dbtype="query">
SELECT * FROM GetDS1Result
UNION
SELECT * FROM GetDS2Result
UNION
SELECT * FROM GetDS3Result
</cfquery>
It runs correctly.
However, when I change to following:
<cfloop query="GetDSResult">
<cfquery name="GetDS#GetDSResult.ds#Result" datasource="#ds.#GetDSResult.ds##">
SELECT name, grade
FROM employee
</cfquery>
</cfloop>
It shows error because of "#ds.#GetDSResult.ds##". It treats "#ds.#GetDSResult.ds##" to "#ds.#GetDSResult.ds##" and shows error.
But I want it to be treated as "#ds.#GetDSResult.ds##"
How can I do it?
The answer to your specific question is to use array notation. Something like #variables['ds' & GetDSResult & 'ds'] might do the trick.
Looking at what you are trying to accomplish, it looks pretty bad. Why do you have all those datasources?