Code Change

[dr223]

Hallo,

The following code shown below works very fine. However, it uses the OVER syntax which brings about the error syntax of OVER SQL construct or statement is not supported.

Please could anyone modify the select query to yield the same result but avoid using the OVER sql construct.

Thanks in Advance.


select prac_no,col_uid,audit_end,[status],stage,audit_start
from
(
select
row_number() over (partition by prac_no order by col_uid desc) as rownum,
*
from
(
SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
) as dt
) as dt2
where rownum = 1

[Alsvha]

Why not give it a shot yourself and then ask specific questions when you run into problems?