quote:

---

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <-, >, >- or when the subquery is used as an expression.

---

CREATE PROCEDURE AcpReport_OpByMachine(@StartDate DateTime, @EndDate DateTime) AS
/* Created by Joe Pool - searches all records for employee records on a machine
   This query is to fill a tree view control with Operator names */
DECLARE @DATE1 DateTime, @DATE2 DateTime, @space char(1)
SET @DATE1=@StartDate
SET @DATE2=@EndDate
SET @space=' '

SELECT DISTINCT T1.SYSTEM_ID as 'System_ID', (SELECT FIRSTNAME+@space+LASTNAME FROM EmployeeInfo WHERE NUM=T1.OP_ID) AS 'Operator' FROM ACP_PARTS T1
WHERE (T1.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT DISTINCT 'Acp_Packout_'+SubString(Cast(DatePart(Year, T2.[Date_Time]) as VarChar(4)), 3, 2) as 'System_ID', (SELECT FIRSTNAME+@space+LASTNAME FROM EmployeeInfo WHERE NUM=T2.OP_ID) AS 'Operator' FROM BOX_DATA T2
WHERE (T2.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT DISTINCT T3.SYSTEM_ID as 'System_ID', (SELECT FIRSTNAME+@space+LASTNAME FROM EmployeeInfo WHERE NUM=T3.OP_ID) AS 'Operator' FROM Final_Check T3
WHERE (T3.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT DISTINCT CASE WHEN 0 < (CharIndex(@space, T4.SYSTEM_ID)) THEN SubString(T4.SYSTEM_ID, 1, CharIndex(@space, T4.SYSTEM_ID)) ELSE T4.SYSTEM_ID END as 'System_ID', T4.OP_ID AS 'Operator' FROM TEST_RESULTS T4
WHERE (T4.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
GO

DECLARE @DATE1 DateTime, @DATE2 DateTime, @space char(1)
SET @DATE1=@StartDate
SET @DATE2=@EndDate
SET @space=' '

SELECT T1.SYSTEM_ID as 'System_ID', NUM AS 'Operator'
FROM ACP_PARTS T1 INNER JOIN EmployeeInfo ON NUM=T1.[OP_ID]
WHERE (T1.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT 'Acp_Packout_'+SubString(Cast(DatePart(Year, T2.[Date_Time]) as VarChar(4)), 3, 2) as 'System_ID', NUM AS 'Operator'
FROM BOX_DATA T2 INNER JOIN EmployeeInfo ON NUM=T2.[OP_ID]
WHERE (T2.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT T3.SYSTEM_ID as 'System_ID', NUM AS 'Operator'
FROM Final_Check T3 INNER JOIN EmployeeInfo ON NUM=T3.[OP_ID]
WHERE (T3.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
	UNION
SELECT CASE WHEN 0 < (CharIndex(@space, T4.SYSTEM_ID)) THEN SubString(T4.SYSTEM_ID, 1, CharIndex(@space, T4.SYSTEM_ID)) ELSE T4.SYSTEM_ID END as 'System_ID', T4.OP_ID AS 'Operator'
FROM TEST_RESULTS T4
WHERE (T4.[DATE_TIME] BETWEEN @DATE1 AND @DATE2)
GO