Any custom function for Queryappend

Question

Queryappend is in Coldfusion - 18 and not in Coldfusion - 16.

Can anyone suggest any custom coldfusoin function for "Queryappend"

Suppose I have 2 queries :

Query-1
select * from user where userid > 10 order by userid asc

Query-2
select * from user where userid < 10 order by userid desc

Query append should return folowing:

userid username 
11  AA
12  BB
13  CC
9   MM
8   NN
7   OO

Thanks in advance

Answer 1

The easiest and most readable solution to emulate a queryAppend() in earlier versions of ColdFusion is by using a query of queries (qoq) and then using the union all feature to append the resultant qoq by not supplying an ORDER BY clause.

<cfquery name="query1" datasource="mydatasource">
    select * from user where userid > 10 order by userid asc
</cfquery>

<cfquery name="query2" datasource="mydatasource">
    select * from user where userid < 10 order by userid desc
</cfquery>

<!--- "Union all" the 2 result sets together in a qoq and don't supply an order by clause  --->
<cfquery name="queryAppend" dbtype="query">
    select * from query1
    union all
    select * from query2
</cfquery>

Here's a working sample of the gist https://trycf.com/gist/484d3ab19f52d81867dacdced47fad09/lucee5?theme=monokai

Answer 2

Here is one way to accomplish that.

<cfscript>
IDList1 = '1,2,3';
IDList2 = '6,5,4';
NameList1 = 'Fred,Wilma,Pebbles';
NameList2 = 'Barney,Betty,Bam,Bam';
Flinstones = QueryNew("UserID,UserName", 'integer,varchar');
Rubbles = QueryNew("UserID,UserName", 'integer,varchar');

for (i = 1; i <=3; i++) {
QueryAddRow(Flinstones);
QuerySetCell(Flinstones, 'UserID', i);

QuerySetCell(Flinstones, 'UserName'
    , ListGetAt(NameList1, i));

QueryAddRow(Rubbles);
QuerySetCell(Rubbles, 'UserID'
    , ListGetAt(IDList2, i));

QuerySetCell(Rubbles, 'UserName'
    , ListGetAt(NameList2, i));
    
}
</cfscript>

<cfquery name="All" dbtype="query">
    select UserID, UserName
    from Flinstones
    order by UserID
</cfquery>

<cfloop query = "Rubbles">
    <cfset QueryAddRow(All)>
    <cfset QuerySetCell(All, 'UserId', UserId,
        All.recordcount)>
        <cfset QuerySetCell(All, 'UserName', UserName,
        All.recordcount)>
    
    
</cfloop>

<cfdump var="#all#">

You can play around with it here.

It wouldn't be that difficult to put this logic into a function that accepts two queries and returns a third.