Appending one set of query results to another set

[deepatpaul]

I'm having difficulty appending the results of one query set to another. I'm getting null values but the recordcount is accurate (adding both queries separate versus the new query total). Here's what I did:

<cfset qRefineSearch = QueryNew("projectID,projectTitle,isNamePrivate") />
<cfscript>
j = 0;
// If one of the primary search queries, loop over the resultset first
if (isQuery(qGetDiseasePrimary) and (qGetDiseasePrimary.RecordCount gt 0))
{
For (i=1;i LTE qGetDiseasePrimary.RecordCount; i=i+1)
{
temp = QueryAddRow(qRefineSearch);
temp = QuerySetCell(qRefineSearch, "projectID", qGetDiseasePrimary.projectID, i);
temp = QuerySetCell(qRefineSearch, "projectTitle", qGetDiseasePrimary.projectTitle, i);
temp = QuerySetCell(qRefineSearch, "isNamePrivate", qGetDiseasePrimary.isNamePrivate, i);
}
}
// Then, add the remaining resultset to complied list (if there are any)
For (i=1;i LTE qSearchRest.RecordCount; i=i+1)
{
j = i + qSearchRest.RecordCount;
temp = QueryAddRow(qRefineSearch);
temp = QuerySetCell(qRefineSearch, "projectID", qSearchRest.projectID, j);
temp = QuerySetCell(qRefineSearch, "projectTitle", qSearchRest.projectTitle, j);
temp = QuerySetCell(qRefineSearch, "isNamePrivate", qSearchRest.isNamePrivate, j);
}
</cfscript>

Ideas? The 2nd For loop is only inserting about half the recordset correctly, and the rest of the results (when viewing output) are null.

 

[webmigit]

Can we see the two queries themselves? And what version of CF are you running... there may be a much better way, friend

Also... you can make a change that in this case could make the script run a lot faster.

<cfset qRefineSearch = QueryNew("projectID,projectTitle,isNamePrivate")>
<cfscript>
    j = 0;
    [blue]qGdpRc = 0;[/blue]
    // If one of the primary search queries, loop over the resultset first
    if (isQuery(qGetDiseasePrimary) and (qGetDiseasePrimary.RecordCount gt 0))
    {
        [blue]qGdpRc = qGetDiseasePrimary.recordcount;[/blue]
        [red]temp = QueryAddRow(qRefineSearch,qGetDiseasePrimary.recordCount);[/red]
        For (i=1;i LTE qGetDiseasePrimary.RecordCount; i=i+1)
        {
            temp = QuerySetCell(qRefineSearch, "projectID", qGetDiseasePrimary.projectID[i], i);
            temp = QuerySetCell(qRefineSearch, "projectTitle", qGetDiseasePrimary.projectTitle[i], i);
            temp = QuerySetCell(qRefineSearch, "isNamePrivate", qGetDiseasePrimary.isNamePrivate[i], i);
        }
    }
    // Then, add the remaining resultset to complied list (if there are any)
    [red]temp = QueryAddRow(qRefineSearch,qSearchRest.recordCount);[/red]
    For (i=1;i LTE qSearchRest.RecordCount; i=i+1)
    {
        // This line: j = i + qSearchRest.RecordCount;
        // probably should be: [b]j = i + [red]qGetDiseasePrimary[/red].recodcount;[/b]
        [blue]// but I added the "patch" variable qGdpRc...[/blue]
        [blue]j = i + [red]qGdpRc[/red];[/blue]
        temp = QuerySetCell(qRefineSearch, "projectID", qSearchRest.projectID[i], i);
        temp = QuerySetCell(qRefineSearch, "projectTitle", qSearchRest.projectTitle[i], i);
        temp = QuerySetCell(qRefineSearch, "isNamePrivate", qSearchRest.isNamePrivate[i], i);
    }
</cfscript>

The two fully red lines add all your query rows for you..

The blue lines patch for the failure of the first query existing, since you use isquery to test it..

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

Hey, mind if I ask if this worked? I thought this was kind of an interesting question so wanted to know if you'd tried my answer.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[deepatpaul]

I actually stumbled upon fixing it myself later on, but I appreciate your help. Though I will try some of your new code fixes to see if I get any speed boost with the resultsets.

 

[r937]

i'd be interested in seeing the queries because you might be able to combine them into one union query and then not have to bother with any of that cf merge code at all

the less cf code, the better

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

That's what I pointed out first, or started to point out...

Query of Query might work too... I use that on one page where I display the same data in two different forms... Ordering by entry method and then ordering by exit method.

Its rather beautiful.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.