Attempting to sort recordset off our iseries

[NVL-Tom]

I have a problem that I would appreciate assistance with.  I have successfuly created a recordset that pulls data off our iSeries.  I have created a table from the recordset, but upon the review of the content of the table by meeting attendees it was suggested that all columns of the table be sortable.  In the past I had used a routine (free for the taking from www.easycfm.com), whereby <cfswitch> is used for sorting purposes.  The SQL portion of the tutorial follows.  However, while it runs against a MS access database, it has problems with the iSeries.  In working with the Starquest people today (vendor for StarSQL) we ran a debug trace and it was observed that for this particular SQL statement, the ORDER BY clause is getting cutting off.  Note the truncation that occurs right after Order BY in the following trace result.

trace result, "WHERE RTYPE = 8 AND RNCTL <> 'Y' AND RRLDTC > 0 AND RRLDTC <= 20090633 AND RESWT <= 5000 ORDER BY".

I also tried a query of the query.  This too failed when I tried the dynamic sort SQL using <cfswitch>.  The error that I received upon running that QOQ was that the ORDER BY was too complex.

Does anyone have any insight on how I might address the dynamic sorting of the recordset off the iSeries?  Thanks!

Example SQL that I used:

<cfquery name="name went here" datasource="datasource went here">
SELECT  *
FROM Table
ORDER BY
<cfswitch expression="#sort#">

  <cfcase value="1">Field1</cfcase>
  <cfcase value="2">Field1 desc</cfcase>
  <cfcase value="3">Field2</cfcase>
  <cfcase value="4">Field2 desc</cfcase>  
</cfswitch>
</cfquery>

[-__cfSearching__-]

NVL-Tom wrote:

However, while it runs against a MS access database, it has problems with the iSeries.  ... we ran a debug trace and it was observed that for this particular SQL statement, the ORDER BY clause is getting cutting off. 

The datasource type really has nothing to do with how ColdFusion generates that sql string.  ColdFusion first evaluates the cfswitch/cfcase statements, then sends the final sql string to the chosen datasource.  So it produces the same string no matter the datasource type.

What is more likely is that your #sort# value did not match any of the cfcase values.  Since there is no default case, nothing is added to the ORDER BY, making it seem like the statement was truncated.

[Dan_Bracuk]

google "javascript table sort" for better options.

[movertom]

Per your suggestion I searched on the web and found a very effective table sorter in JS.  Thanks!

[movertom]

Thanks for taking the time to comment.