4 Replies Latest reply on Jun 20, 2009 10:05 AM by movertom

    Attempting to sort recordset off our iseries

    NVL-Tom Level 1

      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>