使用where子句导致无效列名称错误
问题描述:
我使用SQL Server 2012的使用where子句导致无效列名称错误
我已经写了下面这除了正常工作当我从包括最后一行“其中NomDiff <> 0”查询。
它告诉我NomDiff是一个无效的列名称。我不明白为什么和不知道如何让查询返回只有nomDiff不等于零的行吗?
;with pf as
(
select Name, Sedol, Nominal from tblTempPLF
where FundCode = 'CSGE'
), pc as
(
select Name, Sedol, Nominal from tblTempPCF
where FundCode = 'BTCM'
)
select coalesce(pf.Name, pc.Name) Name, coalesce(pf.Sedol, pc.Sedol) Sedol,
isnull(pf.Nominal,0) PfNom, isnull(pc.Nominal,0) PcNom, isnull(pf.Nominal,0) - isnull(pc.Nominal,0) NomDiff
from pf full outer join pc on pf.Sedol = pc.Sedol
where NomDiff <> 0
答
使用APPLY VALUES
是不必使用子查询的一个很好的方式,或其他CTE:或
WITH pf
AS (SELECT
Name ,
Sedol ,
Nominal
FROM tblTempPLF
WHERE FundCode = 'CSGE'
),
pc
AS (SELECT
Name ,
Sedol ,
Nominal
FROM tblTempPCF
WHERE FundCode = 'BTCM'
)
SELECT
Name = COALESCE(pf.Name, pc.Name),
Sedol = COALESCE(pf.Sedol, pc.Sedol),
PfNom = ISNULL(pf.Nominal, 0),
PcNom = ISNULL(pc.Nominal, 0),
NomDiff = Nom.Diff
FROM pf
FULL OUTER JOIN pc
ON pf.Sedol = pc.Sedol
CROSS APPLY(VALUES(ISNULL(pf.Nominal, 0) - ISNULL(pc.Nominal, 0))) AS Nom(Diff)
WHERE Nom.Diff <> 0;
答
不能在where
子句中使用预先定义alias
。改用计算。
select coalesce(pf.Name, pc.Name) Name,
coalesce(pf.Sedol, pc.Sedol) Sedol,
isnull(pf.Nominal,0) PfNom, isnull(pc.Nominal,0) PcNom,
isnull(pf.Nominal,0) - isnull(pc.Nominal,0) NomDiff
from pf full outer join pc on pf.Sedol = pc.Sedol
where isnull(pf.Nominal,0) - isnull(pc.Nominal,0)<> 0
答
,因为你是在SELECT
创造它不能在WHERE
使用的别名列,更改为:
WHERE isnull(pf.Nominal,0) - isnull(pc.Nominal,0) <> 0
或者你可以使用子查询/ CTE使别名可用于引用它的外部查询。
其原因是WHERE
实际上是在SELECT
之前评估的,因此优化程序会先过滤出行,然后再担心要返回哪些字段。
答
只需添加另一个CTE这样你就可以使用
;with pf as
(
select Name, Sedol, Nominal from tblTempPLF
where FundCode = 'CSGE'
), pc as
(
select Name, Sedol, Nominal from tblTempPCF
where FundCode = 'BTCM'
), anotherOne as /* add another cte */
(
select
coalesce(pf.Name, pc.Name) Name,
coalesce(pf.Sedol, pc.Sedol) Sedol,
isnull(pf.Nominal,0) PfNom,
isnull(pc.Nominal,0) PcNom,
isnull(pf.Nominal,0) - isnull(pc.Nominal,0) NomDiff
from pf full
outer join pc
on pf.Sedol = pc.Sedol
)
select *
from anotherOne
where NomDiff <> 0
答
既然你已经使用CTE
别名,它会更容易只是换行外查询作为另一个CTE
,然后筛选出NomDiff
(别名计算列)。 Alias
不在相同的select语句内,因此要直接使用Alias
,您需要将其包装在subquery
或CTE
中。否则,您也可以直接在where子句中使用计算,就像其他人在这里所建议的那样。
;WITH pf
AS (
SELECT NAME
,Sedol
,Nominal
FROM tblTempPLF
WHERE FundCode = 'CSGE'
)
,pc
AS (
SELECT NAME
,Sedol
,Nominal
FROM tblTempPCF
WHERE FundCode = 'BTCM'
)
,calc
AS (
SELECT coalesce(pf.NAME, pc.NAME) NAME
,coalesce(pf.Sedol, pc.Sedol) Sedol
,isnull(pf.Nominal, 0) PfNom
,isnull(pc.Nominal, 0) PcNom
,isnull(pf.Nominal, 0) - isnull(pc.Nominal, 0) NomDiff
FROM pf
FULL OUTER JOIN pc ON pf.Sedol = pc.Sedol
)
SELECT *
FROM calc
WHERE NomDiff <> 0
另一个查询围绕一个与别名包裹 – Beth