Problem with Complex SQL 2k Query

[williadn48]

OK. I am a newbie. I was given this query and must make mods to it to get it to work. There are 2 halves. The first half queries for volume data. The second half queries for time data. Each query half used to read data from the same tables. Now the query needs to read data from a different time table. When I separate the query halves an run them individually, they both run fine. How do I marry them. Its frustrating. I am at my wits end! HELP!
Error: Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'OperatorID'.

CODE:
SELECT AssociateID,
operatorid,

OpDate = (
CASE
WHEN ip.OpDate IS NULL THEN CONVERT(VARCHAR,et.OpDate,101)
ELSE CONVERT(VARCHAR,ip.OpDate,101)
END),
ip.Scan_Imgs,et.Scan_Secs,
ip.Imgv_Imgs,et.Imgv_Secs,
ip.Indx_mod_Docs,et.Indx_mod_Secs,
ip.Indx_hvy_Docs,et.Indx_hvy_Secs,
ip.Indx_mod2_Docs,et.Indx_mod2_Secs,
ip.Indx_vde_Docs,et.Indx_vde_Secs,
ip.Indx_cs_Docs,et.Indx_cs_Secs,
ip.Indx_lite_Docs,et.Indx_lite_Secs,
ip.Indv_mod_Docs,et.Indv_mod_Secs,
ip.Indv_hvy_Docs,et.Indv_hvy_Secs,
ip.Indv_mod2_Docs,et.Indv_mod2_Secs,
ip.Indv_cs_Docs,et.Indv_cs_Secs,
ip.Indv_lite_Docs,et.Indv_lite_Secs

FROM (SELECT bh.operatorid,
CONVERT(VARCHAR,bh.Datetime,101) AS OpDate,
scan_imgs = SUM(
CASE bh.Activity
WHEN 'BatchScanned' THEN bh.ImagesProcessed
ELSE 0
END),
imgv_imgs = SUM(
CASE bh.Activity
WHEN 'BatchImgVerified' THEN bh.ImagesProcessed
ELSE 0
END),
indx_mod_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndexed' AND qu.Indexing_Type = 'Mod1' THEN bh.DocsProcessed
ELSE 0
END),
indx_hvy_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndexed' AND qu.Indexing_Type = 'Heavy' THEN bh.DocsProcessed
ELSE 0
END),
indx_mod2_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndexed' AND qu.Indexing_Type = 'Mod2' THEN bh.DocsProcessed
ELSE 0
END),
indx_vde_docs = SUM(
CASE
WHEN bh.Activity = 'BatchDCVerifyDE' AND qu.Indexing_Type = 'VerifyDE' THEN bh.DocsProcessed
ELSE 0
END),
indx_cs_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndexed' AND qu.Indexing_Type = 'CS' THEN bh.DocsProcessed
ELSE 0
END),
indx_lite_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndexed' AND qu.Indexing_Type = 'Light' THEN bh.DocsProcessed
ELSE 0
END),
indv_mod_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndVerified' AND qu.Indexing_Type = 'Mod1' THEN bh.DocsProcessed
ELSE 0
END),
indv_hvy_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndVerified' AND qu.Indexing_Type = 'Heavy' THEN bh.DocsProcessed
ELSE 0
END),
indv_mod2_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndVerified' AND qu.Indexing_Type = 'Mod2' THEN bh.DocsProcessed
ELSE 0
END),
indv_cs_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndVerified' AND qu.Indexing_Type = 'CS' THEN bh.DocsProcessed
ELSE 0
END),
indv_lite_docs = SUM(
CASE
WHEN bh.Activity = 'BatchIndVerified' AND qu.Indexing_Type = 'Light' THEN bh.DocsProcessed
ELSE 0
END)
FROM fcBatch ba
INNER JOIN fcBox bo ON ba.boxid = bo.boxid
INNER JOIN fcBatchHistory bh ON ba.batchid = bh.batchid
INNER JOIN fcQueue qu ON bo.QueueID = qu.QueueID
INNER JOIN fcOperator so ON bh.OperatorID = speratorID
INNER JOIN tblECMTimeTrackingMain ett ON so.AssociateID = ett.AssociateID
WHERE bh.Datetime >= '1/1/2007' AND
bh.Datetime < DATEADD(day,1,'5/1/2007') AND
bh.OperatorID = speratorID
GROUP BY bh.operatorid,CONVERT(VARCHAR,bh.Datetime,101)) AS ip
FULL OUTER JOIN
(SELECT ot.associateid,
CONVERT(VARCHAR,ot.EntryDate,101) AS OpDate,
scan_secs = SUM(
CASE ot.FuncArea
WHEN '33' THEN ot.secondselapsed
ELSE 0
END),
imgv_secs = SUM(
CASE ot.FuncArea
WHEN '36' THEN ot.secondselapsed
ELSE 0
END),
indx_mod_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '113' THEN ot.secondselapsed
ELSE 0
END),
indx_hvy_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '115' THEN ot.secondselapsed
ELSE 0
END),
indx_mod2_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '114' THEN ot.secondselapsed
ELSE 0
END),
indx_vde_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '116' THEN ot.secondselapsed
ELSE 0
END),
indx_cs_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '117' THEN ot.secondselapsed
ELSE 0
END),
indx_lite_secs = SUM(
CASE
WHEN ot.FuncArea = '34' AND ot.SubFunc = '112' THEN ot.secondselapsed
ELSE 0
END),
indv_mod_secs = SUM(
CASE
WHEN ot.FuncArea = '35' AND ot.SubFunc = '113' THEN ot.secondselapsed
ELSE 0
END),
indv_hvy_secs = SUM(
CASE
WHEN ot.FuncArea = '35' AND ot.SubFunc = '115' THEN ot.secondselapsed
ELSE 0
END),
indv_mod2_secs = SUM(
CASE
WHEN ot.FuncArea = '35' AND ot.SubFunc = '114' THEN ot.secondselapsed
ELSE 0
END),
indv_cs_secs = SUM(
CASE
WHEN ot.FuncArea = '35' AND ot.SubFunc = '117' THEN ot.secondselapsed
ELSE 0
END),
indv_lite_secs = SUM(
CASE
WHEN ot.FuncArea = '35' AND ot.SubFunc = '112' THEN ot.secondselapsed
ELSE 0
END)
FROM tblECMTimeTrackingMain ot
INNER JOIN fcOperator so ON ot.AssociateID = so.AssociateID
INNER JOIN fcBatchHistory bh ON so.operatorid = bh.operatorid

WHERE ot.EntryDate >= '1/1/2007' AND
ot.EntryDate < DATEADD(day,1,'5/1/2007') AND
ot.AssociateID = so.AssociateID
GROUP BY ot.Associateid, CONVERT(VARCHAR,ot.EntryDate,101)) AS et
ON ip.OpDate = et.OpDate AND
ip.OperatorID = et.OperatorID
ORDER BY AssociateID,OpDate

I suspect the problem is at the end with the "on" clause. There is no operator id in the et code.

 

[jmeckley]

you need to select the OperatorId field in the 2nd subquery. Without this field in the sub query the parent query does not know the column exists.

Jason Meckley
Programmer
Specialty Bakers, Inc.