Using Coalesce fails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using Coalesce fails

Author

Topic

mramey64
Starting Member

7 Posts

Posted - 2014-01-09 : 16:51:43

Hello All,

I am running the attached query in SQL Server 2012. Everything seems to work but I'm missing over 3,000 records(the ones where EmployeeID is null on the tbl_HW_Activities table). I have broken the query down and found that its the last criteria in the where clause that cuts the other records off. The records being lost are the ones that do not have any values in the EmployeeID column. When I run the query without the last Coalesce piece I get all the records. My joins work fine. It's when I plug in the EmpID piece. Can anyone see anything wrong by just looking at my code?

Thanks for any help!
Mike


DECLARE @TrayNbr NVARCHAR(20) = NULL, @JobID NVARCHAR(20) = NULL, @EmpID NVARCHAR(20) = NULL

SET @TrayNbr = ''
SET @JobID = '10876'
SET @EmpID = ''
IF @TrayNbr = ''
SET @TrayNbr = NULL
IF @JobID = ''
SET @JobID = NULL
IF @EmpID = ''
SET @EmpID = NULL
    

SELECT	tbl_HW_Trays.TrayID, dbo.tbl_HW_Jobs.JobNumber, tbl_HW_Jobs.LotCode, tbl_HW_Trays.TrayNumber, tbl_hw_trays.Pieces, (tbl_hw_employees.Firstname +
' ' + tbl_hw_employees.LastName)AS EmployeeName
FROM dbo.tbl_HW_Trays LEFT JOIN dbo.tbl_HW_Activities ON dbo.tbl_HW_Trays.TrayID = dbo.tbl_HW_Activities.TrayID
LEFT JOIN dbo.tbl_HW_Jobs ON dbo.tbl_HW_Activities.JobID = dbo.tbl_HW_Jobs.JobID LEFT JOIN dbo.tbl_HW_Employees ON 
dbo.tbl_HW_Activities.EmployeeID = dbo.tbl_HW_Employees.EmployeeID
WHERE  dbo.tbl_HW_Trays.JobID = COALESCE(@JobID, tbl_HW_Trays.JobID) AND TrayNumber = COALESCE(@TrayNbr, TrayNumber) AND
tbl_HW_Activities.EmployeeID = COALESCE(@EmpID, dbo.tbl_HW_Activities.EmployeeID)
ORDER BY tbl_HW_Trays.JobID, CAST(tbl_hw_Trays.TrayNumber AS INT)

Michael

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-09 : 17:29:58

Shouldn't your where clause reference tbl_HW_Activities instead of tbl_HW_Employees?

Change:
tbl_HW_Employees.EmployeeID = COALESCE(@EmpID, dbo.tbl_HW_Employees.EmployeeID)
To:
tbl_HW_Activities.EmployeeID = COALESCE(@EmpID, dbo.tbl_HW_Activities.EmployeeID)

Activities is the left table in your query, and Employees is the right table.

It's late in the day though, so maybe I'm not thinking this through right.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mramey64
Starting Member

7 Posts

Posted - 2014-01-09 : 18:04:06

Tara I'm sorry it actually does. I was experimenting out of frustration but my actual code does use the Activities table. I use the Activities table join on the Employees table to get the employee name that is working on the tray. It is kicking all the tray records without an employee id (tray is not checked out yet) out of the results.

Thanks Tara
Mike

Michael

mramey64
Starting Member

7 Posts

Posted - 2014-01-09 : 18:05:37

I fixed the code in my first post. Thanks again Tara.

Mike

Michael

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-09 : 18:07:17

Could you show us a data example? It does not have to be real data, just data that illustrates the issue. I'm having a hard time visualizing this one.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:32:05

can you try this modification and see if it works?


DECLARE @TrayNbr NVARCHAR(20) = NULL, @JobID NVARCHAR(20) = NULL, @EmpID NVARCHAR(20) = NULL

SET @TrayNbr = ''
SET @JobID = '10876'
SET @EmpID = ''
IF @TrayNbr = ''
SET @TrayNbr = NULL
IF @JobID = ''
SET @JobID = NULL
IF @EmpID = ''
SET @EmpID = NULL
    

SELECT	tbl_HW_Trays.TrayID, dbo.tbl_HW_Jobs.JobNumber, tbl_HW_Jobs.LotCode, tbl_HW_Trays.TrayNumber, tbl_hw_trays.Pieces, (tbl_hw_employees.Firstname +
' ' + tbl_hw_employees.LastName)AS EmployeeName
FROM dbo.tbl_HW_Trays LEFT JOIN dbo.tbl_HW_Activities ON dbo.tbl_HW_Trays.TrayID = dbo.tbl_HW_Activities.TrayID
AND tbl_HW_Activities.EmployeeID = COALESCE(@EmpID, dbo.tbl_HW_Activities.EmployeeID)
LEFT JOIN dbo.tbl_HW_Jobs ON dbo.tbl_HW_Activities.JobID = dbo.tbl_HW_Jobs.JobID LEFT JOIN dbo.tbl_HW_Employees ON 
dbo.tbl_HW_Activities.EmployeeID = dbo.tbl_HW_Employees.EmployeeID
WHERE  dbo.tbl_HW_Trays.JobID = COALESCE(@JobID, tbl_HW_Trays.JobID) 
AND TrayNumber = COALESCE(@TrayNbr, TrayNumber) 
ORDER BY tbl_HW_Trays.JobID, CAST(tbl_hw_Trays.TrayNumber AS INT)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources