CTE中的对象名称无效 [英] Invalid object name in CTE

问题描述

我正在加入两个不同的表，并且在使用CTE加入第二个表时我获得了无效对象。请让我知道我的加入方式是否正确。



我尝试了什么：

; t1为（
 SELECT DepId，COUNT（EmpId）AS TotalHeadCount FROM Emploee 
 WHERE（datepart（yyyy，DOJ）2005年至2017年）和Status = 0 
 group by DepId 
），
 t2 as（
 SELECT DepId，COUNT（EmpId）AS NewJoinees FROM Emploee 
 WHERE（DATEPART（yyyy，DOJ） = 2017）AND（DATEPART（mm，DOJ）= 01）和datepart（mm，DOJ）> = 12和Status = 0 
 group by DepId 
），
 t3 as（
 SELECT Tobehired AS TOBEHIRED，OpenPosition AS OPENPOSITION，Status FROM Employee1）
 
选择t1.DepId，CASE WHEN TotalHeadCount IS NULL然后'0'ELSE TotalHeadCount END AS TotalHeadCount，NewJoinees IS NULL时的情况那么'0'
 ELSE NewJoinees END AS NewJoinees，Tobehired，OpenPosition，Status 
 from t1 full outer join t2 on t1.DepId = t2.DepId 
 full outer join t3 on t1.DepId = t3 .DepId 

解决方案

您没有在CTE中选择 DepId t3



根据要求，这是我猜测修改后的查询应该是什么。请注意，我说猜测是因为你还没有给我足够的表格模式，样本数据或预期结果形式的任何信息。

;   t1  as （
  SELECT  DepId，COUNT （EmpId） AS  TotalHeadCount  FROM  Emploee 
  WHERE （datepart（yyyy，DOJ）   2005  和  2017 ）和状态= 0 
  group   by  DepId 
），
 t2  as （
  SELECT  DepId，COUNT（EmpId） AS  NewJoinees  FROM  Emploee 
  WHERE （DATEPART（yyyy，DOJ）=  2017 ） AND （DATEPART（mm，DOJ）=  01 ）和 datepart（ mm，DOJ）> =  12  和状态=  0  
  group   by  DepId 
），
 t3  as （
  SELECT  Depid，Tobehired  AS  TOBEHIRED，OpenPosition  AS  OPENPOSITION，状态 FROM  Employee1）
 
 < span class =code-keyword>选择 t1.DepId， CASE   WHEN  TotalHeadCount < span class =code-keyword> IS   NULL   THEN  '  0'   ELSE  TotalHeadCount  END   AS  TotalHeadCount， CASE   WHEN  NewJoinees  IS   NULL  那么 '  0' 
  ELSE  NewJoinees  END   AS  NewJoinees，Tobehired，OpenPosition，Status 
  from  t1  full  外部 加入 t2   t1.DepId = t2.DepId 
 完整 外部  join  t3  on  t1.DepId = t3.DepId 

Hi,
I am Joining two different Tables and I am getting Invalid Object on joining the second table using CTE. Please Let me know whether my way of joining is Correct.

What I have tried:

;with t1 as (
SELECT DepId, COUNT(EmpId) AS TotalHeadCount  FROM Emploee          
WHERE (datepart(yyyy,DOJ) between 2005 and 2017) and Status =0
group by DepId
),
t2 as (
SELECT  DepId,COUNT(EmpId) AS NewJoinees FROM Emploee         
WHERE (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ)  = 01) and datepart(mm,DOJ)>= 12 and Status = 0
group by DepId
) ,
t3 as (
SELECT  Tobehired AS TOBEHIRED,OpenPosition AS OPENPOSITION,Status FROM Employee1 )        
     
Select t1.DepId, CASE WHEN TotalHeadCount IS NULL THEN '0' ELSE TotalHeadCount END AS TotalHeadCount,CASE WHEN NewJoinees IS NULL THEN '0' 
ELSE NewJoinees END AS NewJoinees,Tobehired,OpenPosition,Status
from t1 full outer join t2 on t1.DepId = t2.DepId
full outer join t3 on t1.DepId = t3.DepId

解决方案

You are not selecting DepId in CTE t3

[EDIT] As requested, here is my guess at what the modified query should be. Note that I say "guess" because you have still not given me enough any information in the form of table schemas, sample data or expected results.

;with t1 as (
SELECT DepId, COUNT(EmpId) AS TotalHeadCount  FROM Emploee          
WHERE (datepart(yyyy,DOJ) between 2005 and 2017) and Status =0
group by DepId
),
t2 as (
SELECT  DepId,COUNT(EmpId) AS NewJoinees FROM Emploee         
WHERE (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ)  = 01) and datepart(mm,DOJ)>= 12 and Status = 0
group by DepId
) ,
t3 as (
SELECT  Depid, Tobehired AS TOBEHIRED,OpenPosition AS OPENPOSITION,Status FROM Employee1 )        
     
Select t1.DepId, CASE WHEN TotalHeadCount IS NULL THEN '0' ELSE TotalHeadCount END AS TotalHeadCount,CASE WHEN NewJoinees IS NULL THEN '0' 
ELSE NewJoinees END AS NewJoinees,Tobehired,OpenPosition,Status
from t1 full outer join t2 on t1.DepId = t2.DepId
full outer join t3 on t1.DepId = t3.DepId

这篇关于CTE中的对象名称无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！