July 7, 2016 at 1:24 am
I like to know if this is expected behaviour of SqlServer or if it is a bug.
The error is shown at the derivation of the column SourceDocumentId:
(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
The error given is “Column 'b.SourceCaseId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”.
This does not make sense and an error is NOT given for a similar sub query which is in the same query:
( 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,
It appears that this is a SQLServer SQL parser defect.
Please share your thopughst with me.
The whole query is given below with the sub query giving the error highlighted in red and the subquery that works highlighted in green.
with quarter(startdate, enddate) AS
(
SELECT CONVERT(Date,'20160101', 120) startdate, CONVERT(Date, '20160331', 120) enddate
),
ServiceFileDoc AS
(SELECT xds.SourceDocumentId,
xds.SourceServiceId,
xds.SourceCaseId,
xds.DateAssessmentEnded
FROM dbo.ExtendedDataStore xds
WHERE (xds.SourceDocumentTemplateTitle = 'MH - Intake Assessment' OR xds.SourceDocumentTemplateTitle = 'MH - Non Intake')
)
SELECT b.SourceCaseId,
b.SourceServiceId,
(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,
b.nrmfollowups,
b.nrmfollowupsthisqtr
FROM
(SELECT a.SourceCaseId SourceCaseId,
a.SourceServiceId SourceServiceId,
DATEDIFF(day, sfd.DateAssessmentEnded, a.EarliestEventDateInQtr) duration,
a.nrmfollowups,
a.nrmfollowupsthisqtr
FROM
(SELECT DISTINCT ser.SourceCaseId,
( 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,
x.nrmfollowups,
x.nrmfollowupsthisqtr,
x.EarliestEventDateInQtr
FROM
(SELECT DISTINCT ser.SourceServiceId,
count(DISTINCT eca.SourceEventId) AS nrmfollowups,
count(DISTINCT
CASE
WHEN eca.EventDate >= q.startdate AND eca.EventDate <= q.enddate THEN eca.SourceEventId
ELSE NULL
END) AS nrmfollowupsthisqtr,
MIN(CASE
WHEN eca.EventDate >= q.startdate AND eca.EventDate <= q.enddate THEN eca.EventDate
ELSE NULL
END) EarliestEventDateInQtr
FROM dbo.SERVICES ser
CROSS JOIN quarter q
INNER JOIN dbo.events eca ON ser.SourceServiceId = eca.SourceServiceId
AND eca.eventStatus = 'SHOW'
AND eca.eventDate <= q.enddate
AND eca.EventDescription like '%phone cal%'
INNER JOIN dbo.EventDetailLine etl ON etl.SourceEventId = eca.SourceEventId
AND etl.SourceEventItemId IS NOT NULL
WHERE ser.Serviceid = 1206
GROUP BY ser.SourceServiceId) x
INNER JOIN dbo.Services ser ON x.SourceServiceId = ser.SourceServiceId) a
LEFT JOIN ServiceFileDoc sfd ON sfd.SourceServiceId = a.SourceServiceId) b
July 7, 2016 at 7:59 am
It looks to me like it's having to do an outer-reference for the aggregate in question, in order to resolve the sub-query. The other sub-query doesn't appear to do that.
It's easier to see if you lay out the query a little differently:
with quarter(startdate, enddate) AS
(SELECT CONVERT(Date,'20160101', 120) startdate, CONVERT(Date, '20160331', 120) enddate),
ServiceFileDoc AS
(SELECT
xds.SourceDocumentId,
xds.SourceServiceId,
xds.SourceCaseId,
xds.DateAssessmentEnded
FROM
dbo.ExtendedDataStore xds
WHERE
(xds.SourceDocumentTemplateTitle = 'MH - Intake Assessment'
OR
xds.SourceDocumentTemplateTitle = 'MH - Non Intake'))
SELECT
b.SourceCaseId,
b.SourceServiceId,
(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,
b.nrmfollowups,
b.nrmfollowupsthisqtr
FROM
(SELECT
a.SourceCaseId SourceCaseId,
a.SourceServiceId SourceServiceId,
DATEDIFF(day, sfd.DateAssessmentEnded, a.EarliestEventDateInQtr) duration,
a.nrmfollowups,
a.nrmfollowupsthisqtr
FROM
(SELECT DISTINCT
ser.SourceCaseId,
(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,
x.nrmfollowups,
x.nrmfollowupsthisqtr,
x.EarliestEventDateInQtr
FROM
(SELECT DISTINCT
ser.SourceServiceId,
count(DISTINCT eca.SourceEventId) AS nrmfollowups,
count(DISTINCT
CASE
WHEN eca.EventDate >= q.startdate AND eca.EventDate <= q.enddate THEN eca.SourceEventId
ELSE NULL
END) AS nrmfollowupsthisqtr,
MIN(
CASE
WHEN eca.EventDate >= q.startdate AND eca.EventDate <= q.enddate THEN eca.EventDate
ELSE NULL
END) EarliestEventDateInQtr
FROM
dbo.SERVICES ser
CROSS JOIN quarter q
INNER JOIN dbo.events eca
ON ser.SourceServiceId = eca.SourceServiceId
AND eca.eventStatus = 'SHOW'
AND eca.eventDate <= q.enddate
AND eca.EventDescription like '%phone cal%'
INNER JOIN dbo.EventDetailLine etl
ON etl.SourceEventId = eca.SourceEventId
AND etl.SourceEventItemId IS NOT NULL
WHERE
ser.Serviceid = 1206
GROUP BY
ser.SourceServiceId) x
INNER JOIN dbo.Services ser
ON x.SourceServiceId = ser.SourceServiceId) a
LEFT JOIN ServiceFileDoc sfd
ON sfd.SourceServiceId = a.SourceServiceId) b
Since I don't know what you're trying to do with the query, I can't suggest a solution. But it does look like the parser is reading it correctly.
I'm curious: Why start out with CTEs, and then switch to sub-queries in the From clause? Why not stick to one or the other? That might make the query easier to read and debug.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2016 at 7:31 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply