Thanks for the response.

Both sub queries refer to elements external to them.

The inner subquery refers to ser which is outside of it.

( SELECT serinner1.SourceServiceId

FROM ( SELECT max(serinner.ServiceOpenDate) AS ServiceOpenDate

FROM dbo.SERVICES serinner

WHERE serinner.SourceCaseId = ser.SourceCaseId AND serinner.ServiceOpenDate <= ser.ServiceOpenDate AND serinner.ServiceId = 1207) x1

JOIN dbo.SERVICES serinner1 ON x1.ServiceOpenDate = serinner1.ServiceOpenDate AND serinner1.ServiceId = 1207

WHERE serinner1.SourceCaseid = ser.SourceCaseId) AS SourceServiceId,

The outer subquery which gives the error refers to b which is outside of it.

(SELECT sfdinner1.SourceDocumentId

FROM (SELECT MAX(b.duration) duration

FROM ServiceFileDoc sfdinner

WHERE sfdinner.SourceServiceId = b.SourceServiceId) x2

JOIN ServiceFileDoc sfdinner1 ON x2.duration = b.duration

AND sfdinner1.SourceServiceId = b.SourceServiceId

AND sfdinner1.SourceCaseId = b.SourceCaseId) SourceDocumentId,

Please note that this functionality works in postgresql. Hence it appears to be a bug in SqlServer.

I will have to re-write the query to workaround this issue.

Also your suggestion of using CTE's consistently is good. I didn't think of a CTE for these queries given that they are implemented in the select list.