sql - 为 null 显示无效的列名错误
问题描述
我有一个这样的查询:
DECLARE @stardate date = '31 OCT 2019' ,
@endate date = '31 OCT 2019' ,
@emp_id int = 0,
@id int=1
DECLARE @lateVal as TIME
SET @lateVal = '08:15' -- Set Late Variable Here
;WITH calendar (FromDate) AS (
SELECT @stardate AS FromDate
UNION ALL
SELECT DATEADD(day, 1, FromDate)
FROM Calendar
WHERE FromDate < @endate
)
,
CTE AS
(
select U.Name,U.Adluserid as 'Empid', cast(C.FromDate as date) AS [Date],
min(case when IOType=0 then Edatetime end) as 'IN',
max(case when IOType=1 then Edatetime end) as 'out',
(case when max(Tr.Status)=1 then (select Description from Tbl_Zeo_Status where Id=@id) when max(Tr.Status)=2 then 'Absent as Sick' else 'L' end) as leave_status,
TS.Description,
Tr.Status as statusid
from calendar C
cross join Mx_UserMst U --on Trn.UsrRefcode=U.UserID
left join Mx_ACSEventTrn Trn on cast(Edatetime as date)=cast(C.FromDate as date) AND Trn.UsrRefcode=U.AdlUserID
left join Tbl_Zeo_Empstatus E on Trn.UsrRefcode=E.Emp_Id
left join Tbl_Zeo_EmpTrans Tr on Trn.UsrRefcode=Tr.Emp_Id and cast(Tr.Date as date)=cast(Edatetime as date)
left join Tbl_Zeo_Status TS on Tr.Status= Ts.Id
where cast(C.FromDate as date) between @stardate and @endate
and (@emp_id=0 OR E.Emp_Id=@emp_id) and U.Name is not null and U.UserIDEnbl = 1
group by U.Name,U.Adluserid , C.FromDate ,ts.Description,Tr.Status
)
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS [id],[Empid],[Name],[IN],[OUT],
(case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
when cast([IN] as time) < @lateVal then 'Present' else 'Late' end) as status,[Description],
[statusid]
FROM CTE
order by [Name],[IN]
如果我的描述列为空,我想显示状态列的值,所以我编辑了我的查询,如下所示:
ISNULL( [Description],[status]) as Des
但它显示错误Invalid column name 'status'。
为什么会这样显示
解决方案
您可以使用 asubquery
来获取此值。
;WITH calendar (FromDate) AS (
SELECT @stardate AS FromDate
UNION ALL
SELECT DATEADD(day, 1, FromDate)
FROM Calendar
WHERE FromDate < @endate
)
,
CTE AS
(
select U.Name,U.Adluserid as 'Empid', cast(C.FromDate as date) AS [Date],
min(case when IOType=0 then Edatetime end) as 'IN',
max(case when IOType=1 then Edatetime end) as 'out',
(case when max(Tr.Status)=1 then (select Description from Tbl_Zeo_Status where Id=@id) when max(Tr.Status)=2 then 'Absent as Sick' else 'L' end) as leave_status,
TS.Description,
Tr.Status as statusid
from calendar C
cross join Mx_UserMst U --on Trn.UsrRefcode=U.UserID
left join Mx_ACSEventTrn Trn on cast(Edatetime as date)=cast(C.FromDate as date) AND Trn.UsrRefcode=U.AdlUserID
left join Tbl_Zeo_Empstatus E on Trn.UsrRefcode=E.Emp_Id
left join Tbl_Zeo_EmpTrans Tr on Trn.UsrRefcode=Tr.Emp_Id and cast(Tr.Date as date)=cast(Edatetime as date)
left join Tbl_Zeo_Status TS on Tr.Status= Ts.Id
where cast(C.FromDate as date) between @stardate and @endate
and (@emp_id=0 OR E.Emp_Id=@emp_id) and U.Name is not null and U.UserIDEnbl = 1
group by U.Name,U.Adluserid , C.FromDate ,ts.Description,Tr.Status
)
select id, EmpId, [Name], [IN], [OUT], [status], isnull([Description], [status]) from ( select ROW_NUMBER() OVER(ORDER BY name ASC) AS [id],[Empid],[Name],[IN],[OUT],
(case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
when cast([IN] as time) < @lateVal then 'Present' else 'Late' end) as status, [Description],
[statusid]
FROM CTE) t1
order by [Name],[IN]
或者,您需要重复您的case
陈述。
isnull([Description],(case when cast([IN] as time) is null then cast(leave_status as nvarchar(50))
when cast([IN] as time) < @lateVal then 'Present' else 'Late' end))
推荐阅读
- ios - 代码中的 UITabBarController 不显示导航栏
- android - 更改导航抽屉中菜单标题标题项的文本颜色
- php - PHP忘记密码恢复
- r - 如何按月重新排序数据框的行?
- git - 在 windows 终端中使用 TFVC
- node.js - 尝试使用 API Gateway 和 Lambda 从 S3 提供图像文件会导致文件损坏
- mysql - 有没有办法选择一个值,其他相同的值转换为 null
- javascript - 用javascript检测网页滚动
- react-native - 如果 Firebase 成功 Auth 另一个页面 React Native
- express - 如何调试警告:预期的服务器 HTML 包含匹配项