22 Replies Latest reply on Sep 9, 2009 12:07 PM by Adam Cameron.

    Append two or more query result -CFC

    emmim44 Level 1

      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>

        • 1. Re: Append two or more query result -CFC
          Adam Cameron. Level 5

          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

          • 2. Re: Append two or more query result -CFC
            emmim44 Level 1

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

            • 3. Re: Append two or more query result -CFC
              Adam Cameron. Level 5

              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

              • 4. Re: Append two or more query result -CFC
                emmim44 Level 1

                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.

                • 5. Re: Append two or more query result -CFC
                  Adam Cameron. Level 5

                  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

                  • 6. Re: Append two or more query result -CFC
                    emmim44 Level 1

                    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.

                    • 7. Re: Append two or more query result -CFC
                      Dan Bracuk Level 5

                      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>

                      • 8. Re: Append two or more query result -CFC
                        emmim44 Level 1

                        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>

                        • 9. Re: Append two or more query result -CFC
                          Dan Bracuk Level 5

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

                          • 10. Re: Append two or more query result -CFC
                            emmim44 Level 1

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

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

                            • 11. Re: Append two or more query result -CFC
                              Dan Bracuk Level 5

                              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.

                              • 12. Re: Append two or more query result -CFC
                                emmim44 Level 1

                                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>

                                • 13. Re: Append two or more query result -CFC
                                  Dan Bracuk Level 5

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

                                  • 14. Re: Append two or more query result -CFC
                                    emmim44 Level 1

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

                                    • 15. Re: Append two or more query result -CFC
                                      Dan Bracuk Level 5

                                      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?

                                      • 16. Re: Append two or more query result -CFC
                                        Adam Cameron. Level 5

                                        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

                                        • 17. Re: Append two or more query result -CFC
                                          emmim44 Level 1

                                          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>

                                          • 18. Re: Append two or more query result -CFC
                                            Adam Cameron. Level 5

                                            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

                                            • 19. Re: Append two or more query result -CFC
                                              Dan Bracuk Level 5

                                              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.

                                              • 20. Re: Append two or more query result -CFC
                                                emmim44 Level 1

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

                                                • 21. Re: Append two or more query result -CFC
                                                  emmim44 Level 1

                                                  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>

                                                  • 22. Re: Append two or more query result -CFC
                                                    Adam Cameron. Level 5

                                                    Cool.

                                                     

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

                                                     

                                                    --

                                                    Adam