Syntax Help with Query

[nerdalert1]

Hello all. I am running into an issue with a query I have. I have a query that takes 2 seconds to run it flies and returns a result set. There are 2 fields in the result set that I need to say "WHERE Field1 = Field2". However, it is totally hanging the query up and it runs forever. How can I handle this? I tried it in the WHERE CLAUSE AND A HAVING Clause. If I take the WHERE Clause or same with if I use HAVING clause the query returns 900 records in 2 seconds flat. For example,

SELECT CUSTOMER, DEPARTMENT, DEPARTMENT1, CUSTNAME,
SUM(T1 + T2) AS TA
SUM(T3 + T4) AS TB
FROM CUSTOMER
WHERE DEPARTMENT = DEPARTMENT1
GROUP BY CUSTOMER, DEPARTMENT, DEPARTMENT1, CUSTNAME

 

[maswien]

try this, put a derivied table there,

SELECT v0.strBudAllocA_BusinessUnit AS BusinessUnit,
v0.strBudAllocA_Account AS Account,
v0.strBudAllocA_Department AS Department,
v0.strBudAllocA_ChargeToDepartment AS ChargeToDept,
v0.strBudAllocA_OrigActivityCode AS OrigActivityCode,
v0.strBudAllocA_ActivityCode AS ActivityCode,
SUM(numBudAllocA_Oct) AS Oct,
SUM(numBudAllocA_Nov) AS Nov,
SUM(numBudAllocA_Dec) AS Dec,
SUM(numBudAllocA_Jan) AS Jan,
SUM(numBudAllocA_Feb) AS Feb,
SUM(numBudAllocA_Mar) AS Mar,
SUM(numBudAllocA_Apr) AS Apr,
SUM(numBudAllocA_May) AS May,
SUM(numBudAllocA_Jun) AS Jun,
SUM(numBudAllocA_Jul) AS Jul,
SUM(numBudAllocA_Aug) AS Aug,
SUM(numBudAllocA_Sep) AS Sep,
SUM(numBudAllocA_Oct_NFY) AS Oct_NFY,
SUM(numBudAllocA_Nov_NFY) AS Nov_NFY,
SUM(numBudAllocA_Dec_NFY) AS Dec_NFY

FROM
( select *
from tblBudget_Allocated_A
where strBudAllocA_ChargeToDepartment = strBudAllocA_Department
) v0 INNER JOIN vwTest
ON (v0.strBudAllocA_ActivityCode = vwTest.ActivityCode)
AND (v0.strBudAllocA_Department = vwTest.Department)
GROUP BY v0.strBudAllocA_BusinessUnit,
v0.strBudAllocA_Account,
v0.strBudAllocA_Department,
v0.strBudAllocA_ChargeToDepartment,
v0.strBudAllocA_OrigActivityCode,
v0.strBudAllocA_ActivityCode

 

[maswien]

The idea is to avoid where clause here, you can also try following:

SELECT v0.strBudAllocA_BusinessUnit AS BusinessUnit,
v0.strBudAllocA_Account AS Account,
v0.strBudAllocA_Department AS Department,
v0.strBudAllocA_ChargeToDepartment AS ChargeToDept,
v0.strBudAllocA_OrigActivityCode AS OrigActivityCode,
v0.strBudAllocA_ActivityCode AS ActivityCode,
SUM(numBudAllocA_Oct) AS Oct,
SUM(numBudAllocA_Nov) AS Nov,
SUM(numBudAllocA_Dec) AS Dec,
SUM(numBudAllocA_Jan) AS Jan,
SUM(numBudAllocA_Feb) AS Feb,
SUM(numBudAllocA_Mar) AS Mar,
SUM(numBudAllocA_Apr) AS Apr,
SUM(numBudAllocA_May) AS May,
SUM(numBudAllocA_Jun) AS Jun,
SUM(numBudAllocA_Jul) AS Jul,
SUM(numBudAllocA_Aug) AS Aug,
SUM(numBudAllocA_Sep) AS Sep,
SUM(numBudAllocA_Oct_NFY) AS Oct_NFY,
SUM(numBudAllocA_Nov_NFY) AS Nov_NFY,
SUM(numBudAllocA_Dec_NFY) AS Dec_NFY

FROM tblBudget_Allocated_A
INNER JOIN vwTest
ON (strBudAllocA_ActivityCode = vwTest.ActivityCode)
AND (strBudAllocA_Department = vwTest.Department)
AND (strBudAllocA_ChargeToDepartment = strBudAllocA_Department)
GROUP BY strBudAllocA_BusinessUnit,
strBudAllocA_Account,
strBudAllocA_Department,
strBudAllocA_ChargeToDepartment,
strBudAllocA_OrigActivityCode,
strBudAllocA_ActivityCode