SQL语句连接表是空查询
你好我使用的是加入表查询来获取一些数据..SQL语句连接表是空查询
USE [AccountingSystem_TEST]
GO
/****** Object: StoredProcedure [dbo].[spHRLeaveEntitlementBuildTemp] Script Date: 03/14/2013 09:18:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spHRLeaveEntitlementBuildTemp]
(
@CreatedUserID as nvarchar(9)
,@EmployeeID nvarchar(9)
,@EmpName nvarchar(150)
)
AS
BEGIN
Select A.CodeHRLeaveType,
COALESCE(B.LeaveBalance) LeaveBalance
From HRLeaveType A
LEFT JOIN HRLeaveMaster B
On A.CodeHRLeaveType = B.CodeHRLeaveType
Where B.EmployeeID = @EmployeeID
Delete dbo.HrLeaveMasterTemp
Where UserID = @CreatedUserID
INSERT INTO [dbo].[HrLeaveMasterTemp]
([UserID]
,[EmployeeID]
,[EmpName]
,[CodeHRLeaveType]
,[LeaveBalance]
,[Status]
)
Select
@CreatedUserID
,[EmployeeID]
,@EmpName
,[CodeHRLeaveType]
,[LeaveBalance]
,[Status]
From dbo.[HRLeaveMaster]
Where EmployeeID [email protected]
END
--Select * From HRLeaveMasterTemp
如果leavebalance为null,我需要的表格显示0 ..但声明显示错误。请帮助我..
使用COALESCE
Select A.CodeHRLeaveType,
COALESCE(B.LeaveBalance) LeaveBalance
From HRLeaveType A
LEFT JOIN HRLeaveMaster B
On A.CodeHRLeaveType = B.CodeHRLeaveType
Where B.EmployeeID = @EmployeeID
它仍然给我的错误.. :( – user1617943 2013-03-14 03:17:30
@ user1617943:什么是错误 – ankurtr 2013-03-14 03:19:55
不能?在代码中看到任何错误,生成的错误信息是什么? – 2013-03-14 03:25:08
在甲骨文更换空函数的使用与NVL功能。在MySql中使用 替换null函数与IFNULL函数一起使用。请致电http://www.w3schools.com/sql/sql_isnull.asp。这可能会帮助你。
的说法应该是 -
Select A.CodeHRLeaveType,
IFNULL(B.LeaveBalance,0) LeaveBalance
From HRLeaveType A
LEFT JOIN HRLeaveMaster B
On A.CodeHRLeaveType = B.CodeHRLeaveType
Where B.EmployeeID = @EmployeeID
它的显示s 消息195,级别15,状态10,过程spHRLeaveEntitlementBuildTemp,行12 'IFNULL'不是公认的内置函数名称。 – user1617943 2013-03-14 03:40:56
'选择 \t \t A.CodeHRLeaveType \t \t,B.LeaveBalance \t(LeaveBalance + ISNULL(LeaveBalance,0))\t \t \t \t \t \t \t 从HRLeaveType甲 \t加入HRLeaveMaster B关于A.CodeHRLeaveType = B. CodeHRLeaveType \t Where B.EmployeeID = @ EmployeeID' – user1617943 2013-03-14 03:42:12
它解决了您的问题。我已经完成了从上述链接,并执行SELECT IFNULL(1,0)在我的phpmyadmin.Anyway ..谢谢.. – 2013-03-14 03:47:57
尝试编辑答案,是否有利于你... – 2013-03-14 03:36:40