Copy link to clipboard
Copied
Hi all,
How will i append one and/ or two and/ or three query result in a cfc? I will pass variable which may contain list ={datasourceName1,datasourceName2,datasourceName3}
I am using SQL 2005...All 3 db has same table names...cfargument param is a list which may contain datasourceName1 and/ or datasourceName2 and/ or datasourceName3
Currently I have ...
<cfcomponent>
<cffunction>
<cfquery datasource='datasourceName1' name='q1'>
select * from location
</cfquery>
<cfreturn q1>
<cfquery datasource='datasourceName2' name='q2>
select * from location
</cfquery>
<cfreturn q2>
<cfquery datasource='datasourceName3' name='q3>
select * from location
</cfquery>
<cfreturn q3>
</cffunction>
Copy link to clipboard
Copied
Read up on Query on Query: http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_1.html.
You can UNION queries together, provided the columns can be mapped in a common way (which will probably not be an issue for you).
--
Adam
Copy link to clipboard
Copied
That doesnt help much.... Any other input appreciated....
Copy link to clipboard
Copied
In what way does it not help? Maybe I'm misunderstanding your requirement... you want to append query results together, right? And you'd do that by using a QoQ and unioning them...
Am I misunderstandind your requirement?
--
Adam
Copy link to clipboard
Copied
There could be three different SQL 05 databasese which i need to have a final query to return... Please input if you have idea to built the cfc using the cfloop and list.
Copy link to clipboard
Copied
Firstly, you seem to be a bit stroppy to me, I'm supposing because you're not getting the answer you want in the timeframe you want. I'm just going to ignore that (beyond observing it).
I'm afraid I don't see how your idea with the loops and the queries could possibly work. Each <cfquery> returns a recordset, fine, but one cannot somehow combine that recordset with another <cfquery> recordset automatically. It's not possible. Well: it is. It's completely possible using QoQ to UNION multiple recordsets together, but you don't seem to like that approach, so I'm at a bit of a loss. I do actually wonder why you don't like that approach... maybe you're not articulating your requirements in a way I understand them. To me, it sounds like a good solution to your question.
But let's look at other options.
A <cfquery> can only hit one DSN, and one DSN can only be configured to access on DB (or on Oracle, one USER, I guess...irrelevant to you). To have three DBs accessible via one DSN, you basically need to set up the DSN to one DB, then map ther other two DBs into that DB. This is absolutely doable, so something you should look at. Once mapping the latter two DBs into your first one, you should be able to query all three in one <cfquery>.
--
Adam
Copy link to clipboard
Copied
Not to be rude... but these list of datasources are dynmically populated... which means that it could have one datasource or two or three at most... based on that I need to combine the queries. If one datasource is defined there is no need to have combine queries then...vice versa
I dont like your third and second options. I have doubt that even the QoQ will work since I may query three different datasources in SQL 05.
Copy link to clipboard
Copied
So you are getting a list of datasources? The cfml reference manual shows you how to loop through a list. If you have trouble doing that, feel free to ask specific questions about it.
And all these datasources have a table named location and every table has the same column names and data types? To keep things simple, let's say they do.
To me, the simplest way is this:
Create a cold fusion query named query0 with the column names and data types you need. Don't put any data in it.
When looping through the list of datasources, name your queries query1, query2, etc. If you don't know how to do that, ask.
At the very end do this:
select your fields
from query0
<cfloop from="1" to ="listlen(arguments.datasources" index="i">
union
select your fields
from query#i#
</cfloop>
Copy link to clipboard
Copied
I think you got it Dan...
My old cfc is like this which only takes one datasource...arguments.dsn is same for both function in old case...If i use your method, what will be the datasource name for the query0???
<cfcomponent hint="This cfc goes to Scorecard environment and grabs some stuff">
<cffunction name="Zone" access="public" returntype="Query">
<cfargument name="DSN" required="true" type="string">
<cfquery name="GetZone" datasource="#arguments.DSN#">
SELECT *
FROM ccflocation
</cfquery>
<cfreturn GetZone>
</cffunction>
<cffunction name="Team" access="public" returntype="Query">
<cfargument name="DSN" required="true" type="string">
<cfquery name="GetTeam" datasource="#arguments.DSN#">
SELECT *
FROM ccfteam
</cfquery>
<cfreturn GetTeam>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
Query0 won't have a datasource name. You create it with querynew().
Copy link to clipboard
Copied
I am little lost... do I need to add row to the querynew?
I need this within a function within a cfc...
Copy link to clipboard
Copied
The purpose of query0 is to simplify the syntax of the union query. You do not have to add any rows to it.
QueryNew() is a native cold fusion function. You can use it anywhere.
You could do it without query0. Since you will always have a query1, you can do this
select your fields
from query1
<cfloop index = "i">
union
select your fields
from query#i#
</cfloop>
You'll be selecting from query1 twice, but union queries do not return duplicate rows so you'll be ok.
Copy link to clipboard
Copied
My actual datasource names are sc_fl,sc_si,sc_to....I am totally lost here... Your idea sounds good ...my dynamic datasource could be sc_fl OR sc_fl,sc_si OR sc_fl,sc_si,sc_to OR sc_si.sc_to OR sc_fl,sc_to ...
is this function correct? My thinking is froze now...
<cffunction name="Zone" access="public" returntype="query">
<cfargument name="DSN" required="true" type="string">
<cfset myLoc = QueryNew("")>
select *
from myLoc
<cfloop from="1" to ="#listlen(arguments.DSN)#" index="i">
union
<cfquery name="GetZone" datasource="#i#">
SELECT *
FROM ccflocation
</cfquery>
</cfloop>
<cfreturn myLoc>
</cffunction>
Copy link to clipboard
Copied
That's not correct. You have to run the database queries first. The union query is a QofQ.
Copy link to clipboard
Copied
I dont know how to do it. I am kind of stuck......!!!
Copy link to clipboard
Copied
There are two parts to this. The first is to run the queries against the datasources and the second is to union them together. Your prior post seemed to be an attempt to combine the two and that won't work. You have to do them separately.
Are you able to do the first part?
Copy link to clipboard
Copied
I think you got it Dan...
Right. So you're having problems looping over a list, rather than appending the queries. I don't think you made that particularly clear.
Looping over a list:
http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_j-l_15.html#3205709
So the logic is this:
loop over the list
do a query, give it a name based on the DSN, eg : <cfquery name=q#listEntry#" datasource="#listEntry#">
end loop
// append the queries
<cfquery name="qAll" dbtype="query">
loop over the list
select columns
from q#listEntry#
if it's not the last item in the list
UNION
end if
end loop
</cfquery>
--
Adam
Copy link to clipboard
Copied
Why doesnt this work? What is wrong with it?
And how would you understand this?
if it's not the last item in the list
UNION
end if
<cfcomponent hint="This cfc goes to Scorecard environment and grabs some stuff">
<cffunction name="Zone" access="public" returntype="Query">
<cfset myLoc = QueryNew("ccflocationID,Location_Name", "Cf_SQL_int,cf_sql_varchar")>
<cfargument name="DSN" required="true" type="string">
<cfquery dbtype="query" name="GetZone">
select ccflocationID, Location_Name from myLoc
union
(
<cfloop from="1" to ="#listlen(arguments.DSN)#" index="i">
<cfquery name="GetZone#i#" datasource="#i#">
SELECT ccflocationID, Location_Name
FROM ccflocation
</cfquery>
union
</cfloop>
)
</cfquery>
<cfreturn GetZone>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
Why doesnt this work? What is wrong with it?
One cannot nest <cfquery> tags as far as I know. Also, all a <cfquery> tag does is capture a string to pass to the JDBC driver (eg: a string containing the SQL statement, eg "select * from myTable where [etc]"). What you seem to be trying to do is to merge the SQL string with a recordset, which makes no sense.
If you follow the logic I outlined above - and I think Dan has already said this - you'll see that you need to do all your DB hits first (one after the other, as discrete operations), and then - after you have all your record sets from the DB - use QoQ to UNION together however many record sets you've got.
And how would you understand this?
if it's not the last item in the list
UNION
end if
Say you have n recordsets that you want to union together. You need to generate an SQL string which expresses this:
select someColumns
from recordset1
UNION
select someColumns
from recordset2
UNION
[...]
select someColumns
from recordSetn
(ie: from 1-n record sets)
So do to that, you need to loop from 1 to n times. Within the loop, you need to write a SELECT statement for each recordset (recordset1... recordsetn). And you need a UNION statement between each one.
If you simply output this for each iteration:
select someColumns
from recordSet#n#
UNION
You'd end up with this:
select someColumns
from recordset1
UNION
select someColumns
from recordset2
UNION
[...]
select someColumns
from recordSetn
UNION
See the trailing UNION? That would be an SQL syntax error. So you need to have some logic within your loop so that the UNION is output for every iteration except the last one. So that is what that IF statement is for. Make sense?
--
Adam
Copy link to clipboard
Copied
When I first starting to use cfloop to write union queries, I would use Adam's approach of having some if/else logic at the end to escape the last union. I don't know if it's better, worse, or the same, but I switched to an approach where you escape the extra union by either starting or finishing with a query that returns no rows. Intuitively, it seems faster than making an if/else decision each time through the loop.
Copy link to clipboard
Copied
The idea is accepted... and will try tomorrow...thank u all for now..
Copy link to clipboard
Copied
This works...Thank you Dan and Adam...
<cffunction name="Zone" access="public" returntype="Query">
<cfargument name="DSN" required="true" type="string">
<cfset myLoc = QueryNew("ccflocationID,Location_Name", "CF_SQL_INTEGER,CF_SQL_VARCHAR")>
<!---cfdump var="#arguments#"--->
<cfloop from="1" to ="#listlen(arguments.DSN)#" index="i">
<cfquery name="GetZone#i#" datasource="#listgetat(trim(arguments.DSN),i)#">
SELECT ccflocationID, Location_Name
FROM ccflocation
</cfquery>
</cfloop>
<cfquery dbtype="query" name="GetZone">
select ccflocationID, Location_Name from myLoc
<cfloop from="1" to ="#listlen(arguments.DSN)#" index="i">
union
SELECT ccflocationID, Location_Name
FROM GetZone#i#
</cfloop>
</cfquery>
<cfreturn GetZone>
</cffunction>
Copy link to clipboard
Copied
Cool.
Don't forget to VAR your variables in any function you write.
--
Adam