1 Reply Latest reply: Jun 13, 2012 4:38 AM by Dan Bracuk RSS

    parameter setting in cfquery datasource

    Phinehas1234 Community Member

      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?