ORDER BY clause issue

[Tu...@AlconLabs.com]

Hi,
 
I'm using SQLServer 2000 with MS JDBC driver. THe SQL query statement
below executed and returned a correct result if I ran within SQL Server
Analyzer. But, when I ran it within my Java app using iBATIS, it
complained about ORDER BY on column s.created_dt.
 
Has any one encountered this similar issue before?
 
Exception:
Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/alcon/ezra/persistence/sql/service-request.xml.
--- The error occurred while applying a parameter map.
--- Check the ServiceRequest.getPendingSRCount-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Column name
's.created_dt' is invalid in the ORDER BY clause because it is not
contained in an aggregate function and there is no GROUP BY clause.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column name
's.created_dt' is invalid in the ORDER BY clause because it is not
contained in an aggregate function and there is no GROUP BY clause.
 
SQL Query
   <select id="getAllPendingSRs" resultClass="srObject"
parameterClass="string"> 
      <![CDATA[    
        SELECT s.srvc_request_num AS serviceRequestId,
               s.created_dt AS createdDate,
               s.tca_event AS tcaEvent,
               s.description AS description,
               s.status AS status,
               s.serial_num AS serialNumber,
               r.name AS serviceRegion,
               p.name AS productName
        FROM sr_srvc_request_v s
        LEFT OUTER JOIN sr_srvc_region_v r ON s.srvc_region_id =
r.srvc_region_id
        LEFT OUTER JOIN sr_product_v p ON s.product_id = p.product_id
        LEFT OUTER JOIN sr_activity_v at ON s.srvc_request_num =
at.srvc_request_num
        LEFT JOIN (SELECT a1.wf_audit_id, 
                          a1.srvc_request_num,
                          a1.wf_action_cd,
                          a1.last_updated_dt
                   FROM ra_wf_audit_t a1
                   INNER JOIN (SELECT srvc_request_num, 
                                      MAX(last_updated_dt) AS
last_updated_dt
                               FROM ra_wf_audit_t
                               GROUP BY srvc_request_num) a2
                   ON a1.srvc_request_num = a2.srvc_request_num
                   AND a1.last_updated_dt = a2.last_updated_dt
                  ) derived_table
        ON s.srvc_request_num = derived_table.srvc_request_num
        $whereOrderByClauseStr$              
      ]]>        
    </select>    
 
where $whereOrderByClauseStr$ is dynamically constructed and passed in
as
 
  WHERE COALESCE(derived_table.wf_action_cd,'UNA') = 'UNA' 
  AND s.area = 'Technical Services'
  AND s.sub_area = 'Surgical'
  AND s.srvc_request_num NOT IN (SELECT t1.srvc_request_num
                                 FROM sr_srvc_request_v AS t1
                                 INNER JOIN sr_activity_v AS t2 ON
(t1.srvc_request_num = t2.srvc_request_num)
                                 WHERE t1.created_dt BETWEEN DATEADD(dd,
0, '08/15/2006') 
                                                         AND DATEADD(dd,
1, '08/17/2006')
                                 GROUP BY t1.srvc_request_num
                                 HAVING COUNT(*) = 1
                                 AND SUM(CASE t2.activity_type_id 
                                           WHEN 7 THEN 1 
                                           ELSE 0 END
                                        ) = 1
                                 ) 
    ORDER BY s.created_dt
 
to produce a final SQL query statement as below
 
        SELECT s.srvc_request_num AS serviceRequestId,
               s.created_dt AS createdDate,
               s.tca_event AS tcaEvent,
               s.description AS description,
               s.status AS status,
               s.serial_num AS serialNumber,
               r.name AS serviceRegion,
               p.name AS productName
        FROM sr_srvc_request_v s
        LEFT OUTER JOIN sr_srvc_region_v r ON s.srvc_region_id =
r.srvc_region_id
        LEFT OUTER JOIN sr_product_v p ON s.product_id = p.product_id
        LEFT JOIN (SELECT a1.wf_audit_id, 
                          a1.srvc_request_num,
                          a1.wf_action_cd,
                          a1.last_updated_dt
                   FROM ra_wf_audit_t a1
                   INNER JOIN (SELECT srvc_request_num, 
                                      MAX(last_updated_dt) AS
last_updated_dt
                               FROM ra_wf_audit_t
                               GROUP BY srvc_request_num) a2
                   ON a1.srvc_request_num = a2.srvc_request_num
                   AND a1.last_updated_dt = a2.last_updated_dt
                  ) derived_table
             ON s.srvc_request_num = derived_table.srvc_request_num
        WHERE COALESCE(derived_table.wf_action_cd,'UNA') = 'UNA' 
        AND s.area = 'Technical Services'
        AND s.sub_area = 'Surgical'
        AND s.srvc_request_num NOT IN (SELECT t1.srvc_request_num
                                       FROM sr_srvc_request_v AS t1
                                       INNER JOIN sr_activity_v AS t2 ON
(t1.srvc_request_num = t2.srvc_request_num)
                                       WHERE t1.created_dt BETWEEN
DATEADD(dd, 0, '08/15/2006') 
                                                               AND
DATEADD(dd, 1, '08/17/2006')
                                       GROUP BY t1.srvc_request_num
                                       HAVING COUNT(*) = 1
                                       AND SUM(CASE t2.activity_type_id 
                                                 WHEN 7 THEN 1 
                                                 ELSE 0 END
                                               ) = 1
                                       ) 
        AND s.srvc_request_num NOT IN (SELECT t1.srvc_request_num
                                       FROM sr_srvc_request_v AS t1
                                       INNER JOIN sr_activity_v AS t2 ON
(t1.srvc_request_num = t2.srvc_request_num)
                                       WHERE t1.created_dt BETWEEN
DATEADD(dd, 0, '08/15/2006') 
                                                               AND
DATEADD(dd, 1, '08/17/2006')
                                       GROUP BY t1.srvc_request_num
                                       HAVING COUNT(*) = 1
                                       AND SUM(CASE t2.activity_type_id 
                                                 WHEN 10 THEN 1 
                                                 ELSE 0 END
                                               ) = 1
        
                                      ) 
        ORDER BY s.created_dt
 
Thanks,
Tuan


This e-mail (including any attachments) is confidential and may be legally privileged. If you are not an intended recipient or an authorized representative of an intended recipient, you are prohibited from using, copying or distributing the information in this e-mail or its attachments. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete all copies of this message and any attachments.
Thank you.