Coalesce在SQL Server中不工作 [英] Coalesce Not working in sql server

问题描述

Select tbl_login.userid,tbl_login.name as Employee,

(case when Exists(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid and tbl_attendance.firstlogin is not null and tbl_attendance.date=''2011/03/25'') then ''Present'' else ''Absent'' end) as Status,

(select top 1 COALESCE(tbl_timesheet.description,''Idle'')  from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date=''2011/03/25'' and stoptime is null) as Filename 

from tbl_login where tbl_login.privilege <> ''ADMIN''

其给定描述字段为NULL(如果为null，则不给定"Idle")，即(COALESCE)不起作用


任何人都可以帮忙吗

its giving description field as NULL(if its null,not giving ''Idle''),that is (COALESCE) is not working


Can Anyone help

推荐答案

可能是问题出在您的where子句中.没有什么比它匹配的?如果描述为NULL，则返回空闲"，但是如果在给定日期没有记录与用户ID匹配，该怎么办?

It might be that the problem is your where clause. What is nothing matches it? ''Idle'' is returned if description is NULL, but what if there is no record that matches the userid at a given date?

(select top 1 COALESCE(tbl_timesheet.description,''Idle'')  from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date=''2011/03/25'' and stoptime is null) as Filename

您需要将COALESCE包裹在完整的选择周围.

You need to wrap the COALESCE around the complete select.

COALESCE(select top 1 tbl_timesheet.description  from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date='2011/03/25' and stoptime is null, 'Idle') as Filename

祝你好运！

Good luck!

这篇关于Coalesce在SQL Server中不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！