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

 

[hneal98]

You can try something like this:

SELECT 
	c1.CUSTOMER, 
	c1.DEPARTMENT, 
	c2.DEPARTMENT1, 
	c1.CUSTNAME,
	c1.SUM(T1 + T2) AS TA
	c1.SUM(T3 + T4) AS TB
FROM 
	CUSTOMER c1
	inner join CUSTOMER c2 ON c.DEPARTMENT = c2.DEPARTMENT1
GROUP BY c1.CUSTOMER, c1.DEPARTMENT, c2.DEPARTMENT1, c1.CUSTNAME

 

[nerdalert1]

Here is the exact query. Any help would be great thanks alot. I dont know why its doing what that. This recordset returns exactly 988 records in 2 seconds. Once I add this WHERE Clause or as a Having it hangs completly. Here is that WHERE CLAUSE and below that the actual query:

WHERE strBudAllocA_ChargeToDepartment = strBudAllocA_Department

ACTUAL QUERY THAT FLIES WITHOUT THE ABOVE CLAUSE BUT I NEED THIS CRITERIA IN IT.

SELECT strBudAllocA_BusinessUnit AS BusinessUnit, strBudAllocA_Account AS Account,
strBudAllocA_Department AS Department, strBudAllocA_ChargeToDepartment AS ChargeToDept,strBudAllocA_OrigActivityCode AS OrigActivityCode, 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)
GROUP BY strBudAllocA_BusinessUnit, strBudAllocA_Account, strBudAllocA_Department, strBudAllocA_ChargeToDepartment,strBudAllocA_OrigActivityCode, strBudAllocA_ActivityCode

 

[dbomrrsm]

why are you doing the inner join when it looks like all the info in your select come from tblBudget_Allocated_A if all the info doesnt then the query is wrong.

[blue]DBomrrsm[/blue]

 

[nerdalert1]

Yes your right it all comes from the Allocated A table. I am rebuilding an access based query that is also the same. It is because of the JOIN to the view to only pull those records where those fields =.

 

[dbomrrsm]

ok well try qualifying the where clause by putting the names of the tables before the field names:

WHERE nameoftable.strBudAllocA_ChargeToDepartment = nameoftable.strBudAllocA_Department

[blue]DBomrrsm[/blue]

 

[nerdalert1]

I tried this just now and it also totally hung. I dont understand why its doing this. The query again still takes approx 8 seconds if I run the inside query by itself. But as soon as I try doing anything with the ChargeToDept to Department it drags. This is what I just tried.

SELECT strBudAllocA_BusinessUnit AS BusinessUnit, strBudAllocA_Account AS Account,
strBudAllocA_Department AS Department, strBudAllocA_ChargeToDepartment AS ChargeToDept,strBudAllocA_OrigActivityCode AS OrigActivityCode, 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
WHERE strBudAllocA_ChargeToDepartment IN (SELECT DISTINCT Department from vwTest)
GROUP BY strBudAllocA_BusinessUnit, strBudAllocA_Account, strBudAllocA_Department, strBudAllocA_ChargeToDepartment,strBudAllocA_OrigActivityCode, strBudAllocA_ActivityCode

 

[nerdalert1]

Ya it did not help putting in the table name same thing it runs for over 4 minutes.

 

[dbomrrsm]

which tables are these two fields in ?

strBudAllocA_ChargeToDepartment
and
strBudAllocA_Department

[blue]DBomrrsm[/blue]

 

[hneal98]

Try this one:

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

 

[nerdalert1]

Here is what I did to just limit things. I created a separate view with the resultset that returns in 2 seconds. I checked it again and it runs perfect and fast. Now again when I add in the WHERE clause it runs on forever. Here it is with the Where clause. When I take out the Where clause it runs 2 seconds.

SELECT BusinessUnit, Account, Department, ChargeToDept, OrigActivityCode, ActivityCode,
Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct_NFY, Nov_NFY, Dec_NFY
FROM vwTest2
WHERE ChargeToDept = Department

 

[nerdalert1]

Thanks hneal98 but still no dice it runs on. I am baffled at this point. I wonder if there is a lock or something going on.

 

[hneal98]

Did you look at my suggestion?

 

[nerdalert1]

hneal98 tried it same results. Even just the view is hanging where its straight forward.

 

[dbomrrsm]

stab in the dark but what datatypes are
ChargeToDept
and
Department

[blue]DBomrrsm[/blue]

 

[nerdalert1]

varchar 255's both of them. Good question though I thought of that as well.

 

[nerdalert1]

Wow this worked no idea why.

SELECT BusinessUnit, Account, Department, ChargeToDept, OrigActivityCode, ActivityCode,
Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct_NFY, Nov_NFY, Dec_NFY
FROM vwTest2
WHERE RTRIM(LTRIM(ChargeToDept)) = RTRIM(LTRIM(Department))

 

[nerdalert1]

Thanks all. That was it for some reason. I appreciate all your time and help with this. You all have a nice holiday . Thanks again guys.

 

[dbomrrsm]

its made the comparison only compare what data is there rather than comparing leading and trailing spaces aswell.

Glad you got it sorted - I would look at why the data is like that it shouldn't have any leading or trailing spaces.

[blue]DBomrrsm[/blue]

 

[nerdalert1]

yes exactly I am going to check thanks alot dobmrrssm