Dynamic cfquery order

I am creating another report where the column header has the following in a TR
FILE             APPLICANT         DATE ASSIGNED           STATUS
123456        John Doe             3/22/09                          Inspected
543211        Tom Foolery        4/1/09                            Closed

I want to be able to click on status and each time it sort by a different status. Click it the first time, it will go according to the next letter in the alphabet or untill it finds the next avaialble letter in the alpha where it can sort by it. Then the next time it will move to the next letter untill it cycles back to "A".
I want to be able to sort by date assigned in a similar manner. Code below is all I could find on the web. Tweaked a little before I came here. Can someone explain to me how this can be done please.
<!--- retrieve all alpha's from the db --->
<cfquery name="Assignments" datasource="shs">
    SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
    FROM tblAssignment
    GROUP BY LEFT(status,1)
    ORDER BY LEFT(status,1) ASC
</cfquery>

Open in new window

dudeatworkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gdemariaConnect With a Mentor Commented:
perhaps this..
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
		     , left(status,1) as StatusChar
        FROM assignments
		order by status
    </cfquery>
	<cfset statusList = valueList(getStat.statusChar)> <!--- build a list of all status codes ---->
	<cfset pos = listFindNoCase(statusList, sort)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
	<cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
	  <cfset nextStatus = listGetAt(statusList,pos+1)>
	<cfelse> <!--- last status was picked, so grab the first one --->
	  <cfset nextStatus = listFirst(statusList)>
	</cfif>

Open in new window

0
 
erikTsomikSystem Architect, CF programmer Commented:
what you can do is setup a link with the url
<a href='filename?sort=Applicant'>Applicant</a>

And then in the query
 <cfquery name="Assignments" datasource="shs">
    SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
    FROM tblAssignment
    GROUP BY LEFT(status,1)
<cfif isDefined('url.sort') and url.sort eq 'Applicant'>
 ORDER BY LEFT(status,1) ASC
</cfif>
   
</cfquery>
0
 
dudeatworkAuthor Commented:
That wont work because the status column may have a hundred "Closed" before the next status type like "inspected". Thus you would have to scroll all the way down which defeats the purpose. Ideally, I would like to click on the STATUS column header and it reload in the next avaialble begining character of the status after the initial page load.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
erikTsomikSystem Architect, CF programmer Commented:
or try this

wher status like '#url.variable#%'
0
 
gdemariaCommented:
@Dude, it's a little confusing what you're trying to accomplish.   Ordering is pretty much A-Z or Z-A.   It sounds like you're interested in filtering the contents so you will "hide" the status "Closed" or perhaps you will page-to the next status?   Are we really talking about sorting so that you have Closed at the bottom and Inspected at the top?   That's not an easily accomplished ordering, perhaps a filter or jump-to would work for you?
0
 
gdemariaCommented:
One thing I have done it to place a select box in the header column with all the possible status values, the user selects the desired status, the page redraws only showing records with that status...   ?

Or, instead of filtering to show only that status, you could jump to the page that that status starting...
0
 
dudeatworkAuthor Commented:
Is it possible to pass a variable (Say the letter C) and order by where status values begin with 'C'? If I can order by a passed var then I think this will work.
0
 
gdemariaCommented:

I think you could move all the "C"  to the top and then just show the rest in alphabetical order.   Know what I mean?  If it doesn't begin with "C" then how does it order?

Your order by clause could be something like this...


   order by case when left(status,1) = 'C' then 1 else 2 end, status

That would push all "C" status to the top and then short everything else alphabetically
0
 
dudeatworkAuthor Commented:
Cant use case right now.
Anyway to make this logically sound? the IIF statement is erroring.
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM assignments
        GROUP BY LEFT(status,1)
        order by IIF(left(status,1) = '#sort#',1,0), status
    </cfquery>
0
 
gdemariaCommented:


   iif() is a coldfusion function   your database will not understand it


   Whatever function you wrap around  status (a database column) needs to be understood by your database, not Coldfusion.


  I'm not thinking of a way to do it without case...  what's wrong with case?
0
 
dudeatworkAuthor Commented:
I think because on the dev it's MS Crapcess
 Here is the error
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'case when left(status,1) = 'C' then 1 else 2 end'.

The full query:
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM Assignments
        GROUP BY LEFT(status,1)
        order by case when left(status,1) = '#sort#' then 1 else 2 end, status
    </cfquery>
I feel so close! Arghh!
0
 
gdemariaCommented:
I'm sorry, I didn't know it was MS access

MS access does have an  iif  function...


  not sure what is wrong with the iif  statement... does access require "  instead of single '  

 ?


0
 
dudeatworkAuthor Commented:
Requires single '

I use another IIF statement in a sum in a cfquery. I think I have the wrong syntax for the order by with the IIF. Thoughts?
0
 
gdemariaCommented:
perhaps change the group by from

GROUP BY LEFT(status,1)

to

group by status


Your order by has 'status' in it so may cause a problem with the group...

You could also try removing "status" from the end of the order by as a test...

or trying the order by without grouping :)


what is the error message?
0
 
dudeatworkAuthor Commented:
Ok, so this works.
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM Assignments
        GROUP BY LEFT(status,1)
        order by IIF(left(status,1) = '#sort#',1,0)
    </cfquery>

However, it doesnt give me the data only the first instance of the Sorted letter, say C for example.
0
 
dudeatworkAuthor Commented:
Ok guys I'm reall close. The following works, However, If I choose "C" the first time I click the link, how can I tell it to go the next available letter in status? Here is the code...
<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    *************************************************
    I think the solutions lies right here!
    <!---<cfif #sort# eq #getstat.status#></cfif>--->
    *************************************************
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#sort#',1,0) DESC
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>

<cfoutput query="Assignments">
 <a href="selectBox.cfm?sort=#status#">#status#</a><br />
</cfoutput>
0
 
dudeatworkAuthor Commented:
Ok, what if im using the whole status 'Closed', 'Cancelled', 'Inspected' and so on? Notice in my above query that it's sorting by the whole status string. Do I just drop the left() in the distinct query? If that snippet you just added did by the entire string then we've got it! I am so ready to move on to another task...
0
 
gdemariaCommented:
Right, so the Closed and Cancelled both starting with "C" are causing grief... is that right?

So I would think you could remove the left( ) function... but I would think you would need to remove it everyone including the order by clause, etc...

So from now on it will map against the entire word
0
 
dudeatworkAuthor Commented:
Just when I think it's going to be the grand finale it stops working. I implemented that snippet and now it does nothing but display the same results set.

Here is the full code...
<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
      <cfset pos = listFindNoCase(statusList, sort)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
      <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
        <cfset nextStatus = listGetAt(statusList,pos+1)>
      <cfelse> <!--- last status was picked, so grab the first one --->
        <cfset nextStatus = listFirst(statusList)>
      </cfif>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextStatus#',1,0) ASC
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>
<a href="selectBox.cfm?sort=#status#">Pick Next Status</a><br />
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>
0
 
dudeatworkAuthor Commented:
Sorry, wrong snippet... This code does not choose anything other than 'closed' or 'inspected'. No 'new', 'cancelled', etc.

<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
      <cfset pos = listFindNoCase(statusList, sort)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
      <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
        <cfset nextStatus = listGetAt(statusList,pos+1)>
      <cfelse> <!--- last status was picked, so grab the first one --->
        <cfset nextStatus = listFirst(statusList)>
      </cfif>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextStatus#',1,0)
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>
<cfoutput>
<a href="selectBox.cfm?sort=#nextstatus#">Pick Next Status</a><br />
</cfoutput>
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>
0
 
dudeatworkAuthor Commented:
Got it!!! Here is the final solution...

<cfif isDefined('nextstatus')>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextstatus#',1,0) DESC
    </cfquery>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
    <cfset pos = listFindNoCase(statusList, nextstatus)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
    <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
      <cfset nextStatus = listGetAt(statusList,pos+1)>
    <cfelse> <!--- last status was picked, so grab the first one --->
      <cfset nextStatus = listFirst(statusList)>
    </cfif>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)>
    <cfset nextStatus = listFirst(statusList)>
</cfif>
<cfoutput>
<a href="selectBox.cfm?nextstatus=#nextstatus#">Pick Next Status</a><br />
</cfoutput>
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>

THANKS TO ALL, ESPECIALLY _AGX_!
0
 
gdemariaCommented:
excellent, glad you got it working !

 ... I didn't see _agx_ on this thread?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.