Append two or more query result -CFC

Report · Sep 07, 2009

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 ...

select * from location

</cfquery>

select * from location

</cfquery>

select * from location

</cfquery>

</cffunction>

Report · Sep 07, 2009

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

Report · Sep 07, 2009

That doesnt help much.... Any other input appreciated....

Report · Sep 07, 2009

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

Report · Sep 08, 2009

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.

Report · Sep 08, 2009

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

Report · Sep 08, 2009

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.

Report · Sep 08, 2009

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

union

select your fields

from query#i#

</cfloop>

Report · Sep 08, 2009

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>

Report · Sep 08, 2009

Query0 won't have a datasource name. You create it with querynew().

Report · Sep 08, 2009

I am little lost... do I need to add row to the querynew?

I need this within a function within a cfc...

Report · Sep 08, 2009

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

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.

Report · Sep 08, 2009

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

union

      <cfquery name="GetZone" datasource="#i#">
        SELECT *
        FROM ccflocation
      </cfquery>
</cfloop>

Report · Sep 08, 2009

That's not correct. You have to run the database queries first. The union query is a QofQ.

Report · Sep 08, 2009

I dont know how to do it. I am kind of stuck......!!!

Report · Sep 08, 2009

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?

Report · Sep 08, 2009

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

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

Report · Sep 08, 2009

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

<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>

</cfcomponent>

Report · Sep 08, 2009

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

Report · Sep 08, 2009

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.

Report · Sep 08, 2009

The idea is accepted... and will try tomorrow...thank u all for now..

Report · Sep 09, 2009

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")>


<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>

Report · Sep 09, 2009

Cool.

Don't forget to VAR your variables in any function you write.

--

Adam

Adobe Community

Append two or more query result -CFC