August 15, 2019 at 3:12 pm
Hey All,
I'm hoping to get some insight as to what could be wrong with this portion of a query I've been trying to run which has been erroring out with "Each GROUP BY expression must contain at least one column that is not an outer reference" or when I try modifying it, I'm receiving an "Incorrect syntax near ')' " error. Nearly losing my mind over this. I appreciate any advice on this. Thanks!:
DECLARE @StartDate AS datetime = 1/1/2010
DECLARE @CancelCutoffDate AS datetime = @StartDate
DECLARE @RunDate AS datetime = GetDate()
IF OBJECT_ID('tempdb.dbo.#TempAssetClassDetails_CS2') IS NOT NULL
DROP TABLE dbo.#TempAssetClassDetails_CS2;
CREATE TABLE #TempAssetClassDetails_CS2(
[EnrollmentID] [nvarchar] (max) NOT NULL
,[CoverageID] [int] NOT NULL
,[Code] [nvarchar] (50) NULL
,[AssetValue] [money] NULL
,[AssetDescription] [nvarchar] (200) NULL);
IF OBJECT_ID('tempdb..##VESValidEnrollList') IS NOT NULL
DROP TABLE dbo.##VESValidEnrollList;
CREATE TABLE ##VESValidEnrollList (
EnrollmentID nvarchar(50) null,
CoverageID int null,
PolicyTransID nvarchar(50) null,
EfftvDt datetime null,
EndDt datetime null,
MaturityDt datetime null,
AssetTotValue money null,
InsuredValueMax money null);
WITH AssetClassCode_by_Enrollments AS (
SELECT pac.AssetClassCode
,vec.PCMAssetClassId
,vec.EnrollmentID
,vec.CoveragePeriod AS CoverageID
,sum(vec.AssetValue) AS AssetValue
FROM ExtVESEnrollmentCoverage vec
INNER JOIN ExtPCMAssetClass pac ON vec.PCMAssetClassId =
pac.AssetClassId
GROUP BY pac.AssetClassCode
,vec.PCMAssetClassId
,vec.EnrollmentID
,vec.CoveragePeriod)
,unique_results AS (
SELECT EnrollmentID, --AssetClassDescription,
CoverageID,
MAX(AssetClassCode) AS Code,
AssetValue
FROM AssetClassCode_by_Enrollments
WHERE CONCAT(EnrollmentID, CoverageID, AssetValue) IN (SELECT CONCAT (EnrollmentID
,CoverageID
,MAX(AssetValue)) AssetValue FROM AssetClassCode_by_Enrollments
GROUP BY EnrollmentID ,CoverageID)
GROUP BY EnrollmentID,
CoverageID,
AssetValue)
INSERT INTO #TempAssetClassDetails_CS2
SELECT DISTINCT
EnrollmentID
,CoverageID
,Code
,AssetValue
,(SELECT MAX(ISNULL(AssetClassDescription, 'not found in MasterAssetClass table'))
FROM ExtPCMAssetClass pac
WHERE pac.AssetClassCode = unique_results.code) AS AssetDescription
FROM unique_results
order by EnrollmentID;
WITH SESDMEnrollmentCoverage AS
(SELECT ec.EnrollmentID
,ec.CoveragePeriod
,ept.PSPolicyTransID --AS PolicyTransID
,DATEADD(dd, DATEDIFF(dd, 0, min(Convert(date,ec.ContractEffectiveDate))), 0) as EfftvDt
,CASE WHEN min(ec.CancelDate) < max(ec.ContractMaturityDate) THEN DATEADD(dd, DATEDIFF(dd, 0, min(Convert(date,ec.CancelDate))), 0) ELSE DATEADD(dd, DATEDIFF(dd, 0, max(Convert(date,ec.ContractMaturityDate))), 0) END as EndDt
,max(convert(date,ec.ContractMaturityDate)) as MaturityDt
,SUM(Convert(money,ec.AssetValue)) as AssetTotValue
,MAX(Convert(money,psc.PolicySchemaCoveragePerOccurrenceMax)) as InsuredValueMax
FROM dbo.ExtVESEnrollmentCoverage ec
INNER JOIN dbo.ExtVESEnrollmentPolicyTrans ept ON ec.EnrollmentID = ept.EnrollmentID
AND ec.CoveragePeriod = ept.CoverageID
INNER JOIN dbo.ExtPCMPolicySchemaCoverage psc ON psc.PolicySchemaCoverageId = ept.CoverageID
GROUP BY ec.EnrollmentID
,ec.CoveragePeriod
,ept.PSPolicyTransID
,(Convert(money,psc.PolicySchemaCoveragePerOccurrenceMax))
HAVING min(ec.ContractEffectiveDate) < getdate())
--HAVING min(ec.ContractEffectiveDate) < @RunDate)
, VESEnrollData AS(
SELECT /*DISTINCT*/
fip.PolicyNumber
,p.ProductCode
,TACD.Code AS AssetClassCode
,TACD.AssetDescription AS AssetClassDescription
,psc.PolicySchemaCoverageName AS CoverageTypeName
,vec.EnrollmentID
,vec.CoveragePeriod AS CoverageID
,ire.ContractNumber AS CustomerAccountNumber
,min(Cast((CAST(ib.InvoiceAccountingYear AS NCHAR(4)) + N'-' + RIGHT
(N'0' + CAST(ib.InvoiceAccountingMonth AS NVARCHAR(2)),2) + N'-05')
AS smalldatetime)) AS FirstInvDt
,min(vec.CancelDate) OVER(PARTITION BY vec.EnrollmentID,
vec.CoveragePeriodID) as CancelDt
,max(im.BilledMonths) AS MaxMonthsInvoiced
,Coalesce(vec.AssetCity, vec.EnrollmentCity) AS City
,Coalesce(vec.AssetState, vec.EnrollmentState) AS StateProvince
,Coalesce(vec.AssetCountry, vec.EnrollmentCountry) AS Country
,Coalesce(vec.AssetPostalCode, vec.EnrollmentPostalCode) AS Zip
FROM ExtFSEInvoicePolicy fip
LEFT OUTER JOIN ExtPCMPolicySchema ps ON fip.PcmPolicyId =
ps.PolicySchemaPolicyId
INNER JOIN ExtVESEnrollmentCoverage vec ON vec.PCMPolicyID =
ps.PolicySchemaPolicyId
LEFT OUTER JOIN ExtFSEPercentPremiumReported ppr ON vec.EnrollmentID =
ppr.SourceID
AND vec.CoveragePeriodID = ppr.SourceCoveragePeriod
LEFT OUTER JOIN ExtFSELastMonthEarnings lme ON ppr.PcmPolicyId =
lme.PcmPolicyId
AND ppr.ReportDateYearMonth = lme.CurrentAccountingYearMonth
AND lme.approvaldate IS NOT NULL
INNER JOIN ExtPCMAssetClass ac ON ac.AssetClassId = vec.PCMAssetClassId
INNER JOIN ExtPCMProduct p ON ps.PolicySchemaProductId = p.ProductId
INNER JOIN ExtPCMPolicySchemaCoverage psc ON vec.ContainerID =
psc.PolicySchemaCoverageContainerId
INNER JOIN ExtFSEInvoiceRiskEntity ire ON vec.EnrollmentID = ire.SourceId
INNER JOIN ExtFSERiskEntity re ON ire.RiskEntityId = re.Id
INNER JOIN ExtFSEInvoiceableMonth im ON re.id = im.RiskEntityId
INNER JOIN ExtFSEInvoiceable ib ON re.Id = ib.RiskEntityId
INNER JOIN #TempAssetClassDetails_CS2 AS TACD ON ire.sourceid =
TACD.EnrollmentID
AND ire.SourceCoveragePeriod = TACD.CoverageID
GROUP BY vec.EnrollmentID,
vec.CoveragePeriod,
vec.CoveragePeriodID,
fip.PolicyNumber,
p.ProductCode,
ire.ContractNumber,
psc.PolicySchemaCoverageName,
TACD.Code,
TACD.AssetDescription,
ppr.ReportDateYearMonth,
vec.CancelDate,
ire.CoverageEndDate,
ire.CoverageStartDate,
im.BilledMonths,
Coalesce(vec.AssetCity, vec.EnrollmentCity),
Coalesce(vec.AssetState, vec.EnrollmentState),
Coalesce(vec.AssetCountry, vec.EnrollmentCountry),
Coalesce(vec.AssetPostalCode, vec.EnrollmentPostalCode))
,VESValidEnrollList AS(
SELECT
EnrollmentID,
CoveragePeriod, --AS CoverageID,
PSPolicyTransID, --AS PolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
SUM(AssetTotValue) AS AssetTotValue,
COALESCE(InsuredValueMax, 0) as InsuredValueMax
FROM SESDMEnrollmentCoverage
WHERE EfftvDt <= EndDt
and EndDt >= @CancelCutoffDate
GROUP BY EnrollmentID,
CoveragePeriod,
PSPolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
AssetTotValue,
COALESCE(InsuredValueMax, 0))
--VES Enrollments query
SELECT --DISTINCT
'VES' AS SourceName
,CONVERT(nvarchar(50), ed.EnrollmentID) as EnrollmentID
,CASE WHEN ed.EnrollmentID is null THEN 'Client' ELSE 'Asset' END as
HierarchyLevel -- same as for FctExposureDtl
,ed.PolicyNumber as PolicyAgreementNumber
,convert(nvarchar(61), vel.PSPolicyTransID) as PolicyTransID
,c.sCustomerID as ClientAccountID
,ed.CustomerAccountNumber
,CASE WHEN pr.sProductID = '9095' THEN CONVERT(nvarchar(5), '1095')
ELSE CONVERT(nvarchar(5), pr.sProductID) END AS ProductID
,COALESCE(UPPER(CASE WHEN RTRIM(LTRIM(dbo.fn_clean_string
(ed.AssetClassCode))) = '' THEN 'UNKNOWN' ELSE UPPER(RTRIM(LTRIM
(dbo.fn_clean_string(ed.AssetClassCode)))) END), 'UNKNOWN') AS
AssetClassCode
,COALESCE(UPPER(CASE WHEN RTRIM(LTRIM(dbo.fn_clean_string
(ed.AssetClassDescription))) = '' THEN 'UNKNOWN' ELSE UPPER(RTRIM
(LTRIM(dbo.fn_clean_string(ed.AssetClassDescription))))
END), 'UNKNOWN') AS AssetClassDescription
,ed.CoverageTypeName
,CASE WHEN ec.EnrollmentID is null THEN 'N' ELSE 'Y' END as
AssetAddressFlag
,ed.City
,ed.StateProvince AS RegionCode
,ed.Zip AS PostalCode
,vel.CoveragePeriod
,vel.EfftvDt
,vel.EndDt
,vel.MaturityDt
,COALESCE(CASE WHEN DATEDIFF(DAY, ec.ContractEffectiveDate,
ec.ContractMaturityDate) / (365/12) < 1 THEN 1 ELSE DATEDIFF(DAY,
ec.ContractEffectiveDate, ec.ContractMaturityDate) / (365/12) END,
-1) AS CoverageTerm
,CASE WHEN ed.CancelDt IS NULL THEN 'NC' ELSE 'UN' END AS
CoverageCancelReason
,CASE WHEN ed.MaxMonthsInvoiced <> 0 AND ed.FirstInvDt IS NOT NULL T
THEN ed.FirstInvDt ELSE NULL END as FirstInvDt
,(SELECT SUM(vel2.AssetTotValue)
FROM VESValidEnrollList AS vel2
WHERE vel.EnrollmentID = vel2.EnrollmentID
AND vel.CoveragePeriod = vel2.CoveragePeriod
GROUP BY vel2.EnrollmentID,
vel2.CoveragePeriod,
vel.EnrollmentID,
vel.CoveragePeriod) AS EquipmentValue
,vel.InsuredValueMax as LiabilityLimit
FROM VESEnrollData ed
INNER JOIN VESValidEnrollList vel ON ed.EnrollmentID = vel.EnrollmentID
AND ed.CoverageID = vel.CoveragePeriod
LEFT OUTER JOIN dbo.ExtVESEnrollmentCoverage ec ON ed.EnrollmentID =
ec.EnrollmentID
LEFT OUTER JOIN dbo.ExtPRSDEPolicy p ON ed.PolicyNumber = p.sPolicyID
LEFT OUTER JOIN dbo.ExtPRSDECustomer c ON p.iCustomerKey = c.iCustomerKey
LEFT OUTER JOIN dbo.ExtPRSDEProduct pr ON p.iProductKey = pr.iProductKey
LEFT OUTER JOIN dbo.ExtJWProductSegment ps ON pr.sProductID = ps.sProductID
WHERE (pr.sProductID = '9095' OR ps.sProductID IS NOT NULL)
AND ed.EnrollmentID = '3986'
GROUP BY ed.EnrollmentID,
ec.EnrollmentID,
ed.PolicyNumber,
vel.PSPolicyTransID,
c.sCustomerID,
ed.CustomerAccountNumber,
pr.sProductID,
ed.AssetClassCode,
ed.AssetClassDescription,
ed.CoverageTypeName,
ed.City,
ed.StateProvince,
ed.Zip,
vel.CoveragePeriod,
vel.EfftvDt,
vel.EndDt,
vel.MaturityDt,
ec.ContractEffectiveDate,
ec.ContractMaturityDate,
ed.CancelDt,
ed.MaxMonthsInvoiced,
FirstInvDt,
vel.AssetTotValue,
vel.InsuredValueMax,
vel.EnrollmentID
August 15, 2019 at 3:30 pm
These are the 5 'group by' columns in the select list:
EnrollmentID
,CoveragePeriod
,PSPolicyTransID
,EfftvDt
,EndDt
These are the 4 'group by' columns in the 'group by' clause:
EnrollmentID
,CoveragePeriod
,PSPolicyTransID
,(Convert(money,psc.PolicySchemaCoveragePerOccurrenceMax))
What happens if you replace your current 'group by' clause with 1,2,3,4,5?
WITH SESDMEnrollmentCoverage AS
(SELECT ec.EnrollmentID
,ec.CoveragePeriod
,ept.PSPolicyTransID --AS PolicyTransID
,DATEADD(dd, DATEDIFF(dd, 0, min(Convert(date,ec.ContractEffectiveDate))), 0) as EfftvDt
,CASE WHEN min(ec.CancelDate) < max(ec.ContractMaturityDate) THEN DATEADD(dd, DATEDIFF(dd, 0, min
(Convert(date,ec.CancelDate))), 0) ELSE DATEADD(dd, DATEDIFF(dd, 0, max(Convert
(date,ec.ContractMaturityDate))), 0) END as EndDt
,max(convert(date,ec.ContractMaturityDate)) as MaturityDt
,SUM(Convert(money,ec.AssetValue)) as AssetTotValue
,MAX(Convert(money,psc.PolicySchemaCoveragePerOccurrenceMax)) as InsuredValueMax
FROM dbo.ExtVESEnrollmentCoverage ec
INNER JOIN dbo.ExtVESEnrollmentPolicyTrans ept ON ec.EnrollmentID = ept.EnrollmentID
AND ec.CoveragePeriod = ept.CoverageID
INNER JOIN dbo.ExtPCMPolicySchemaCoverage psc ON psc.PolicySchemaCoverageId = ept.CoverageID
GROUP BY 1,2,3,4,5
HAVING min(ec.ContractEffectiveDate) < getdate())
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2019 at 3:58 pm
Sorry, the 1,2,3,... syntax only works in sort by in Sql Server. Can't tell if the syntax parses because the tables aren't in my test instance. Please try:
WITH SESDMEnrollmentCoverage AS
(SELECT ec.EnrollmentID
,ec.CoveragePeriod
,ept.PSPolicyTransID --AS PolicyTransID
,DATEADD(dd, DATEDIFF(dd, 0, min(Convert(date,ec.ContractEffectiveDate))), 0) as EfftvDt
,CASE WHEN min(ec.CancelDate) < max(ec.ContractMaturityDate) THEN DATEADD(dd, DATEDIFF(dd, 0, min
(Convert(date,ec.CancelDate))), 0) ELSE DATEADD(dd, DATEDIFF(dd, 0, max(Convert
(date,ec.ContractMaturityDate))), 0) END as EndDt
,max(convert(date,ec.ContractMaturityDate)) as MaturityDt
,SUM(Convert(money,ec.AssetValue)) as AssetTotValue
,MAX(Convert(money,psc.PolicySchemaCoveragePerOccurrenceMax)) as InsuredValueMax
FROM dbo.ExtVESEnrollmentCoverage ec
INNER JOIN dbo.ExtVESEnrollmentPolicyTrans ept ON ec.EnrollmentID = ept.EnrollmentID
AND ec.CoveragePeriod = ept.CoverageID
INNER JOIN dbo.ExtPCMPolicySchemaCoverage psc ON psc.PolicySchemaCoverageId = ept.CoverageID
GROUP BY ec.EnrollmentID
,ec.CoveragePeriod
,ept.PSPolicyTransID
,DATEADD(dd, DATEDIFF(dd, 0, min(Convert(date,ec.ContractEffectiveDate))), 0)
,CASE WHEN min(ec.CancelDate) < max(ec.ContractMaturityDate) THEN DATEADD(dd, DATEDIFF(dd, 0, min
(Convert(date,ec.CancelDate))), 0) ELSE DATEADD(dd, DATEDIFF(dd, 0, max(Convert
(date,ec.ContractMaturityDate))), 0) END
HAVING min(ec.ContractEffectiveDate) < getdate())
Fwiw, The psuedo-Sql in SAS lets you use 1,2,3,... in group by.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2019 at 5:24 pm
This was removed by the editor as SPAM
August 15, 2019 at 5:27 pm
This was removed by the editor as SPAM
August 15, 2019 at 5:29 pm
The spam filter will not let you post "select *star*" which is kind of annoying on a Sql site.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2019 at 5:30 pm
This was removed by the editor as SPAM
August 15, 2019 at 5:31 pm
This was removed by the editor as SPAM
August 15, 2019 at 5:34 pm
It won't let me post code. Please add a select statement that references the common table expression.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2019 at 5:41 pm
@scdecade, sorry for the difficulty. Here is the part of the query that is references the cte, which happens to be another cte:
,VESValidEnrollList AS(
SELECT
EnrollmentID,
CoveragePeriod, --AS CoverageID,
PSPolicyTransID, --AS PolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
SUM(AssetTotValue) AS AssetTotValue,
COALESCE(InsuredValueMax, 0) as InsuredValueMax
FROM SESDMEnrollmentCoverage
WHERE EfftvDt <= EndDt
and EndDt >= @CancelCutoffDate
GROUP BY
EnrollmentID,
CoveragePeriod,
PSPolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
AssetTotValue,
COALESCE(InsuredValueMax, 0))
August 15, 2019 at 5:49 pm
So it's turtles all the way. At some point you must have a statement that access the cte's yes? Because if you define a cte and don't use it sql returns: Incorrect syntax near ')'
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2019 at 7:23 pm
You posted 2 code blocks. It appears there is an issue with the second one. Where you define the cte:
VESValidEnrollList AS(
SELECT
EnrollmentID,
CoveragePeriod, --AS CoverageID,
PSPolicyTransID, --AS PolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
SUM(AssetTotValue) AS AssetTotValue,
COALESCE(InsuredValueMax, 0) as InsuredValueMax
FROM SESDMEnrollmentCoverage
WHERE EfftvDt <= EndDt
and EndDt >= @CancelCutoffDate
GROUP BY EnrollmentID,
CoveragePeriod,
PSPolicyTransID,
EfftvDt,
EndDt,
MaturityDt,
AssetTotValue,
COALESCE(InsuredValueMax, 0))
The second to last line of code "AssetTotValue," shouldn't be there. That column is defined with an aggregate function.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply