Solved

Dynamic cfquery order

Posted on 2009-04-16
22
313 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:dudeatwork
  • 11
  • 9
  • 2
22 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24159734
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
 

Author Comment

by:dudeatwork
ID: 24160036
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
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24160172
or try this

wher status like '#url.variable#%'
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24164701
@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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24164711
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
 

Author Comment

by:dudeatwork
ID: 24167705
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24167744

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
 

Author Comment

by:dudeatwork
ID: 24167976
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24168032


   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
 

Author Comment

by:dudeatwork
ID: 24168087
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24168192
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dudeatwork
ID: 24168364
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24168624
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
 

Author Comment

by:dudeatwork
ID: 24168851
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
 

Author Comment

by:dudeatwork
ID: 24169253
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 24170085
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
 

Author Comment

by:dudeatwork
ID: 24170339
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24170427
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
 

Author Comment

by:dudeatwork
ID: 24170796
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
 

Author Comment

by:dudeatwork
ID: 24170842
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
 

Author Comment

by:dudeatwork
ID: 24171469
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24171541
excellent, glad you got it working !

 ... I didn't see _agx_ on this thread?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now