Syntax Inquiry

  • daniness

    SSCrazy

    Points: 2890

    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

    • This topic was modified 10 months, 3 weeks ago by  daniness.
  • scdecade

    SSC Eights!

    Points: 825

    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())

    • This reply was modified 10 months, 3 weeks ago by  scdecade. Reason: Put column lists in code editor
  • daniness

    SSCrazy

    Points: 2890

    @scdecade, thanks for your input. When I try GROUP BY 1,2,3,4,5 , it still returns the "Each GROUP BY expression must contain at least one column that is not an outer reference" error.

  • scdecade

    SSC Eights!

    Points: 825

    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.

  • daniness

    SSCrazy

    Points: 2890

    @scdecade, when I try the above that you provided, I'm still getting the same errors. Any other suggestions, please?

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • scdecade

    SSC Eights!

    Points: 825

    The spam filter will not let you post "select *star*" which is kind of annoying on a Sql site.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • scdecade

    SSC Eights!

    Points: 825

    It won't let me post code.  Please add a select statement that references the common table expression.

  • daniness

    SSCrazy

    Points: 2890

    @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))
  • scdecade

    SSC Eights!

    Points: 825

    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 ')'

  • daniness

    SSCrazy

    Points: 2890

    @scdecade, yes, the statement does access the ctes. I've edited my original post to include the entire query. Sorry, I should've done this at first.

  • scdecade

    SSC Eights!

    Points: 825

    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.

     

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply