Query sort in coldfusion

Question

I have a group of data from excel file read the file and take the result in a variable for create a new excel with what are wrong and i try

local.sheetData =queryNew("FirstName,LastName,Address,Email,Phone,DOB,Role");
        for(row in data){
            queryAddRow(local.sheetData);
         
            if(row["First Name"] != '' || row["Last Name"] != '' || row["Address"] != '' || row["Email"] != '' || row["Phone"] != '' || row["DOB"] != '' || row["Role"] != ''){
                if(row["First Name"] === '' || row["Last Name"] === '' || row["Address"] === '' || row["Email"] === '' || row["Phone"] === '' || row["DOB"] === '' || row["Role"] === ''){

                    local.endresult = arrayToList(local.nullMessage)
                    querySetCell(local.sheetData, "FirstName", row["First Name"]);
                    querySetCell(local.sheetData, "LastName", row["Last Name"]);
                    querySetCell(local.sheetData, "Address", row["Address"]);
                    querySetCell(local.sheetData, "Email", row["Email"]);
                    querySetCell(local.sheetData, "Phone", row["Phone"]);
                    querySetCell(local.sheetData, "DOB", row["DOB"]);
                    querySetCell(local.sheetData, "Role", row["Role"]);
                   
                }else{
                    querySetCell(local.sheetData, "FirstName", row["First Name"]);
                    querySetCell(local.sheetData, "LastName", row["Last Name"]);
                    querySetCell(local.sheetData, "Address", row["Address"]);
                    querySetCell(local.sheetData, "Email", row["Email"]);
                    querySetCell(local.sheetData, "Phone", row["Phone"]);
                    querySetCell(local.sheetData, "DOB", row["DOB"]);
                    querySetCell(local.sheetData, "Role", row["Role"]);
                    
                }
            } 
        }
        
       return local.sheetData;

it working fine and get the result like

i wand sort this query and show the any cell is empty that row show first. how to sort the query in coldfusion ?

Could it be that data already is a query object in your case? How does WriteDump(data) look like? — Tomalak
– Tomalak, Commented Mar 29, 2022 at 12:36
That's what local.sheetData looks like. I asked what data looks like. — Tomalak
– Tomalak, Commented Mar 30, 2022 at 6:17
It looks like the issue you are trying to solve is importing an excel sheet and its importing blank rows that have no data, check out github.com/cfsimplicity/spreadsheet-cfml can probably accomplish your task easier than your current solution — ssteinbeck
– ssteinbeck, Commented Mar 30, 2022 at 23:59

Tomalak · Accepted Answer · 2022-03-29 12:13:57Z

3

The easiest way to sort a query in ColdFusion is to use the Query-of-Queries feature.

tag syntax

<cfset myQuery = ...>
<cfquery name="sortedQuery" dbtype="query">
  SELECT * FROM myQuery ORDER BY col1, col2 DESC;
</cfquery>

cfscript syntax

var myQuery = ...;
var sortedQuery = queryExecute("
   SELECT * FROM myQuery ORDER BY col1, col2 DESC;
", {}, {dbtype: "query"});

In any case - if you insert rows in the right order, you don't need to sort anything, so pre-sorting data before your loop would be the most efficient option you have.

answered Mar 29, 2022 at 12:13

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shambu Over a year ago

my datas are coming from an excel file

rrk Over a year ago

@Shambu That does not matter. Once you have read the data into a Coldfusion query variable, you can use that variable in Query of Query logic (to create a sorted query like the answer).

Collectives™ on Stack Overflow

Query sort in coldfusion

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related