August 22, 2006 at 10:53 am
Hi,
Would someone please explain and help me solve an exception below. I don't understand why it complainted about the ORDER BY clause on s.created_dt column.
Exception:
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 statement:
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
)
ORDER BY s.created_dt
Thanks,
Tuan
August 22, 2006 at 1:39 pm
I don't have an answer, and it's hard for us to examine this without the table structures and the data, however..
What I would do here is to break it down into views to debug it. The query within the IN() could be put in a view, and the derived_table could be in another view.
Your query might be easier to work with then, and you may find your answer, if you don't have one by now.
Good luck.
August 22, 2006 at 2:46 pm
What I don't get is ... you are LEFT JOINING the derived_table, but not using any of the columns in derived_table in the resultset.
As you know, if you LEFT JOIN something, you get all rows on the other side. Therefore what is the point of the entire "LEFT JOIN () derived_table" construct if it doesn't:
- provide any columns for the resultset or
- fiilter the resultset like an INNER JOIN would
August 22, 2006 at 6:34 pm
Comment ORDER BY and you'll get bunch of other errors.
Fix them and restore ORDER BY. It will work OK.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply