Sorting fails on grouped fields

[curiousgally]

I have written custom sql with operation binding as below:

<DataSource ID="adb" table="adb" serverType="sql" dbName="jdbc/mdads" showPrompt="false">
    <fields>
        <field name="ORIG" type="text" required="false" title="Origin"/>
        <field name="ORG_CTY_CD" type="text" required="false" title="Origin City"/>
        <field name="ORG_CNTRY_CD" type="text" required="false" title="Origin Country"/>
        <field name="WACO" type="text" required="false" title="Origin Area"/>

        <field name="DEST" type="text" required="false" title="Destination"/>
        <field name="DEST_CTY_CD" type="text" required="false" title="Destination City"/>
        <field name="DEST_CNTRY_CD" type="text" required="false" title="Destination Country"/>
        <field name="WACD" type="text" required="false" title="Destination Area"/>
        <field name="OPAIRL" type="text" required="false" title="Operating Airline"/>
        <field name="LEG2" type="text" required="false" title="LEG1"/>
        <field name="LEG2AIRL" type="text" required="false" title="LEG1 Airline"/>
        <field name="LEG3" type="text" required="false" title="LEG2"/>
        <field name="LEG3AIRL" type="text" required="false" title="LEG2 Airline"/>

        <field name="LEG4" type="text" required="false" title="LEG3"/>
        <field name="LEG4AIRL" type="text" required="false" title="LEG3 Airline"/>

        <field name="DOMAIRL" type="text" required="false" title="Marketing Airline"/>
        <field name="UnadjBookings" type="number" required="false" title="Unadjusted Bookings"/>
        <field name="bookings" type="number" required="false" title="Bookings"/>
        <field name="fare" type="number" required="false" title="Average Fare"/>
        <field name="revenue" type="number" required="false" title="Revenue"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="summaryReport">
            <selectClause>
                orig,ORG_CTY_CD,ORG_CNTRY_CD,WACO,dest, DEST_CTY_CD,DEST_CNTRY_CD,WACD,opairl,domairl, sum(upax) as UnadjBookings, sum(pax_count) as bookings,round(sum(adb_revenue)/sum(pax_count),2) as fare, sum(adb_revenue) as revenue
            </selectClause>
            <whereClause>
                security_id in ($criteria.security_id)  and dates between $criteria.fromDate and $criteria.toDate and
                #if($criteria.originType == "Airport Code") ORIG #elseif($criteria.originType == "City Code") ORG_CTY_CD #elseif($criteria.originType == "Country Code") ORG_CNTRY_CD #else WACO #end in
                #set ($count = 1)
                #foreach($item in $criteria.orig)
                    #if($count != 1) , #else (  #end  #set ($count=$count+1) '$item'
                #end  ) and
                 #if($criteria.destinationType == "Airport Code") DEST #elseif($criteria.destinationType == "City Code") DEST_CTY_CD #elseif($criteria.destinationType == "Country Code") DEST_CNTRY_CD #else WACD #end in
                #set ($count = 1)
                #foreach($item in $criteria.dest)
                    #if($count != 1) , #else (  #end  #set ($count=$count+1) '$item'
                #end  )
            </whereClause>
            <groupClause>
                orig,ORG_CTY_CD,ORG_CNTRY_CD,WACO,dest, DEST_CTY_CD,DEST_CNTRY_CD,WACD,opairl,domairl
            </groupClause>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="detailReport">
            <selectClause>
                opairl,domairl,LEG2,LEG2AIRL,LEG3,LEG3AIRL,LEG4,LEG4AIRL, sum(upax) as UnadjBookings,sum(pax_count) as
                bookings,round(sum(adb_revenue)/sum(pax_count),2) as fare, sum(adb_revenue) as
                revenue
            </selectClause>
            <whereClause>
                security_id in ($criteria.security_id) AND orig=$criteria.orig and dest=$criteria.dest and
                dates between $criteria.fromDate and $criteria.toDate
            </whereClause>
            <groupClause>
                opairl,domairl,LEG2,LEG2AIRL,LEG3,LEG3AIRL,LEG4,LEG4AIRL
            </groupClause>
        </operationBinding>
    </operationBindings>

</DataSource>

so i see the following query when I load data to the ListGrid:

SELECT COUNT(*)
FROM
  (SELECT orig,
    ORG_CTY_CD,
    ORG_CNTRY_CD,
    WACO,
    dest,
    DEST_CTY_CD,
    DEST_CNTRY_CD,
    WACD,
    opairl,
    domairl,
    SUM(upax)                                AS UnadjBookings,
    SUM(pax_count)                           AS bookings,
    ROUND(SUM(adb_revenue)/SUM(pax_count),2) AS fare,
    SUM(adb_revenue)                         AS revenue
  FROM adb
  WHERE security_id IN ('120')
  AND dates BETWEEN 200901 AND 200901
  AND ORG_CNTRY_CD IN ( 'US' )
  AND DEST         IN ( 'BOM' )
  GROUP BY orig,
    ORG_CTY_CD,
    ORG_CNTRY_CD,
    WACO,
    dest,
    DEST_CTY_CD,
    DEST_CNTRY_CD,
    WACD,
    opairl,
    domairl
  )

But when i try to sort on the bookings column, I get error saying invalid character "ADB"."BOOKINGS". But the Sql that is run for Sorting has an issue:

SELECT orig,
  ORG_CTY_CD,
  ORG_CNTRY_CD,
  WACO,
  dest,
  DEST_CTY_CD,
  DEST_CNTRY_CD,
  WACD,
  opairl,
  domairl,
  SUM(upax)                                AS UnadjBookings,
  SUM(pax_count)                           AS bookings,
  ROUND(SUM(adb_revenue)/SUM(pax_count),2) AS fare,
  SUM(adb_revenue)                         AS revenue
FROM adb
WHERE security_id IN ('120')
AND dates BETWEEN 200901 AND 200901
AND ORG_CNTRY_CD IN ( 'US' )
AND DEST         IN ( 'BOM' )
GROUP BY orig,
  ORG_CTY_CD,
  ORG_CNTRY_CD,
  WACO,
  dest,
  DEST_CTY_CD,
  DEST_CNTRY_CD,
  WACD,
  opairl,
  domairl
ORDER BY adb.bookings DESC

So, the order by clause is getting screwed up.
How do I get around this??

Thanks.

[Isomorphic]

Your select clause is going to need to contain all columns on which you allow sorting. Note that one way to avoid having to override the entire <selectClause> is to use dataSourceField.customSelectExpression.

[curiousgally]

Thanks for the pointers.

The following attribute

qualifyColumnNames="false"

Since I don't have multiple tables this solves my problem.

Also can you please respond to this thread.

http://forums.smartclient.com/showthread.php?t=16206

Thanks.