时间戳

首页 > 解决方案 > 用于划分列总数和格式结果的公式无效

sql - 用于划分列总数和格式结果的公式无效

问题描述

我在下面有以下 SQL 代码，它返回一条错误消息，指出该AvgPYs列无效。这也影响了我的Auth专栏。我的公式有问题吗？任何帮助表示赞赏。

SELECT [tblCeiling].[Proj Code], [tblCeiling].[Act Code], [tblCeiling].[Cost Ctr], [tblCeiling].[Date], [tblCeiling].[Ref2], [tblCeiling].[Analyst], [tblCeiling].[Type], [tblCeiling].[B or O], [tblCeiling].[Jul], [tblCeiling].[Aug], 
                  [tblCeiling].[Sep], [tblCeiling].[Oct], [tblCeiling].[Nov], [tblCeiling].[Dec], [tblCeiling].[Jan], [tblCeiling].[Feb], [tblCeiling].[Mar], [tblCeiling].[Apr], [tblCeiling].[May], [tblCeiling].[Jun], [tblCeiling].[Perm], [tblCeiling].[Temp], [tblCeiling].[LimitedTerm], 
                  [tblCeiling].[LTDate], [tblCeiling].[Sal_Rate], [tblCeiling].[New], [Perm] + [Temp] + [LimitedTerm] AS Monthly, Format(([tblCeiling].[Jul] + [tblCeiling].[Aug] + [tblCeiling].[Sep] + [tblCeiling].[Oct] + [tblCeiling].[Nov] + [tblCeiling].[Dec] + [tblCeiling].[Jan] +
                  [tblCeiling].[Feb] + [tblCeiling].[Mar] + [tblCeiling].[Apr] + [tblCeiling].[May] + [tblCeiling].[Jun]) / 12, '0.0####') AS AvgPYs, 
                  ((([Sal_Rate] * [AvgPYs]) * 1000) / 1000) AS Auth, [Dollar Adj] + [Auth] AS Budget, [tblCeiling].[Import], [tblCeiling].[Dollar Adj], [tblCeiling].[OngoingOrOneTime], [tblCeiling].[OneTimeEndingDate]
FROM     (SELECT DISTINCT *
                  FROM      [tblactcode]) AS [tblactcode] RIGHT JOIN
                      (SELECT DISTINCT *
                       FROM      [tblCeiling]) AS [tblCeiling] ON [tblactcode].[Act Code] = [tblCeiling].[Act Code]
WHERE  ((([tblCeiling].[Jul]) = iif([jul] IS NULL, 0, [jul])) AND (([tblCeiling].[Aug]) = iif([aug] IS NULL, 0, [aug])) AND (([tblCeiling].[Sep]) = iif([sep] IS NULL, 0, [sep])) AND (([tblCeiling].[Oct]) = iif([oct] IS NULL, 0, [oct])) AND (([tblCeiling].[Nov]) = iif([nov] IS NULL,
                   0, [nov])) AND (([tblCeiling].[Dec]) = iif([dec] IS NULL, 0, [dec])) AND (([tblCeiling].[Jan]) = iif([jan] IS NULL, 0, [jan])) AND (([tblCeiling].[Feb]) = iif([feb] IS NULL, 0, [feb])) AND (([tblCeiling].[Mar]) = iif([mar] IS NULL, 0, [mar])) AND (([tblCeiling].[Apr]) 
                  = iif([apr] IS NULL, 0, [apr])) AND (([tblCeiling].[May]) = iif([may] IS NULL, 0, [may])) AND (([tblCeiling].[Jun]) = iif([jun] IS NULL, 0, [jun])) AND (([tblCeiling].[Import]) = 0))
ORDER BY [tblCeiling].[Proj Code], [tblCeiling].[Cost Ctr], [tblCeiling].[Date]

无效名称

这是我所指的特定行：

Format(([tblCeiling].[Jul] + [tblCeiling].[Aug] + [tblCeiling].[Sep] + [tblCeiling].[Oct] + [tblCeiling].[Nov] + [tblCeiling].[Dec] + [tblCeiling].[Jan] +
                  [tblCeiling].[Feb] + [tblCeiling].[Mar] + [tblCeiling].[Apr] + [tblCeiling].[May] + [tblCeiling].[Jun]) / 12, '0.0####') AS AvgPYs,

标签： sqlsql-servertsql

解决方案

您遇到的具体问题是尝试在您正在计算的同一范围内使用计算 - 这是不可能的。您只能访问外部查询中的计算值。

或者一个巧妙的解决方案是使用CROSS APPLY它允许您重用计算，如下所示。一般来说，这样做是：

select -- existing columns before AvgPYs
  , AvgPYs
  -- , some formula which depends on AvgPYs 
from (
    -- existing query
) C -- C is an acceptable short alias for Ceiling
cross apply (
    values (formula)
) X (AvgPYs)

在你的情况下，我认为以下是正确的：

SELECT C.[Proj Code], C.[Act Code], C.[Cost Ctr], C.[Date], C.[Ref2], C.[Analyst], C.[Type], C.[B or O], C.[Jul], C.[Aug] 
    , C.[Sep], C.[Oct], C.[Nov], C.[Dec], C.[Jan], C.[Feb], C.[Mar], C.[Apr], C.[May], C.[Jun], C.[Perm], C.[Temp], C.[LimitedTerm]
    , C.[LTDate], C.[Sal_Rate], C.[New], [Perm] + [Temp] + [LimitedTerm] AS Monthly
    , X.AvgPYs 
    , Y.Auth
    , [Dollar Adj] + Y.Auth AS Budget, C.[Import], C.[Dollar Adj], C.[OngoingOrOneTime], C.[OneTimeEndingDate]
FROM (
    SELECT DISTINCT *
    FROM [tblactcode]
) AS AC
RIGHT JOIN (
    SELECT DISTINCT *
    FROM [tblCeiling]
) AS C ON AC.[Act Code] = C.[Act Code]
CROSS APPLY (
    VALUES (Format((C.[Jul] + C.[Aug] + C.[Sep] + C.[Oct] + C.[Nov] + C.[Dec] + C.[Jan] +
    C.[Feb] + C.[Mar] + C.[Apr] + C.[May] + C.[Jun]) / 12, '0.0####'))
) AS X (AvgPYs)
CROSS APPLY (
    VALUES (((([Sal_Rate] * X.AvgPYs) * 1000) / 1000))
) Y (Auth)
WHERE (((C.[Jul]) = iif([jul] IS NULL, 0, [jul])) AND ((C.[Aug]) = iif([aug] IS NULL, 0, [aug])) AND ((C.[Sep]) = iif([sep] IS NULL, 0, [sep])) AND ((C.[Oct]) = iif([oct] IS NULL, 0, [oct])) AND ((C.[Nov]) = iif([nov] IS NULL,
    0, [nov])) AND ((C.[Dec]) = iif([dec] IS NULL, 0, [dec])) AND ((C.[Jan]) = iif([jan] IS NULL, 0, [jan])) AND ((C.[Feb]) = iif([feb] IS NULL, 0, [feb])) AND ((C.[Mar]) = iif([mar] IS NULL, 0, [mar])) AND ((C.[Apr]) 
    = iif([apr] IS NULL, 0, [apr])) AND ((C.[May]) = iif([may] IS NULL, 0, [may])) AND ((C.[Jun]) = iif([jun] IS NULL, 0, [jun])) AND ((C.[Import]) = 0)
)
ORDER BY C.[Proj Code], C.[Cost Ctr], C.[Date];

注意：表别名的一个关键目的是对表有一个简短的引用。看看使用较短的别名阅读起来有多容易。

推荐阅读