Highlighted

How to order a query by a column added using QueryAddColumn()?

New Here ,
Jul 23, 2014

Copy link to clipboard

Copied

I have a query ("GetTotals"). I'm adding a column there from the other query ("GetTheList") using the standard code:

          <CFSET myArray = ArrayNew(1)>

            <cfloop query="GetTheList">

                <CFSET TotalJobs = GetTheList.Total>

                <CFSET x = ArrayAppend(myArray,TotalJobs)>

            </cfloop>

            <CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>

<CFOUTPUT QUERY="GetTotals"> shows everything the way I need.

Except for one problem: How I order the output by that added column?

Simple re-order (below) works for one column only but not for all of them.

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC

                </cfquery>


All ideas are appreciated!

Not entirely sure what you mean by "works for one column only but not for all of them".  Are you adding multiple columns?  Or is that you're also wanting to do secondary ordering, e.g. have them ordered by Totals then by Name?

So for instance your query is already ordered on Name, you've just added your new Totals column.  You should be able to do:

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC, Name ASC

      </cfquery>


PS: when you're using <cfset> to call functions like this, you don't have to assign them to a variable:

<CFSET x = ArrayAppend(myArray,TotalJobs)>

<CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


arrayAppend returns a boolean, queryAddColumn returns an integer indicating the number of the new column.  In most cases these values aren't used; you can just do this:

<CFSET ArrayAppend(myArray,TotalJobs)>

<CFSET QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


Views

265

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to order a query by a column added using QueryAddColumn()?

New Here ,
Jul 23, 2014

Copy link to clipboard

Copied

I have a query ("GetTotals"). I'm adding a column there from the other query ("GetTheList") using the standard code:

          <CFSET myArray = ArrayNew(1)>

            <cfloop query="GetTheList">

                <CFSET TotalJobs = GetTheList.Total>

                <CFSET x = ArrayAppend(myArray,TotalJobs)>

            </cfloop>

            <CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>

<CFOUTPUT QUERY="GetTotals"> shows everything the way I need.

Except for one problem: How I order the output by that added column?

Simple re-order (below) works for one column only but not for all of them.

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC

                </cfquery>


All ideas are appreciated!

Not entirely sure what you mean by "works for one column only but not for all of them".  Are you adding multiple columns?  Or is that you're also wanting to do secondary ordering, e.g. have them ordered by Totals then by Name?

So for instance your query is already ordered on Name, you've just added your new Totals column.  You should be able to do:

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC, Name ASC

      </cfquery>


PS: when you're using <cfset> to call functions like this, you don't have to assign them to a variable:

<CFSET x = ArrayAppend(myArray,TotalJobs)>

<CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


arrayAppend returns a boolean, queryAddColumn returns an integer indicating the number of the new column.  In most cases these values aren't used; you can just do this:

<CFSET ArrayAppend(myArray,TotalJobs)>

<CFSET QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


Views

266

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 23, 2014 0
Engaged ,
Jul 24, 2014

Copy link to clipboard

Copied

Not entirely sure what you mean by "works for one column only but not for all of them".  Are you adding multiple columns?  Or is that you're also wanting to do secondary ordering, e.g. have them ordered by Totals then by Name?

So for instance your query is already ordered on Name, you've just added your new Totals column.  You should be able to do:

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC, Name ASC

      </cfquery>


PS: when you're using <cfset> to call functions like this, you don't have to assign them to a variable:

<CFSET x = ArrayAppend(myArray,TotalJobs)>

<CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


arrayAppend returns a boolean, queryAddColumn returns an integer indicating the number of the new column.  In most cases these values aren't used; you can just do this:

<CFSET ArrayAppend(myArray,TotalJobs)>

<CFSET QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>


Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 24, 2014 0
el_sim LATEST
New Here ,
Jul 24, 2014

Copy link to clipboard

Copied

Thanks a lot for your reply. Yes, you're right: this code does work as desired. I've fixed my problem - it was outside this piece of code.

Your comment helped to direct my attention to the other places. Thanks again.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 24, 2014 0