MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

SELECTing into local var, incorrect syntax near =


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 28th, 2012, 03:59 PM
Grimey Grimey is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2011
Posts: 14 Grimey User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 h 25 m 27 sec
Reputation Power: 0
SELECTing into local var, incorrect syntax near =
Hello all. I am having a problem with a query which has been working until I made one (seemingly minor) modification, by adding a local variable @atc_count. This is only the second SQL statement I've made using local variables, so I'm sure I'm missing something really simple, but after a few hours now I've not been able to solve it.

I've searched Google and DevShed for information about this error as it pertains to setting a variable, or the COUNT function, but haven't seen anything yet that shows me the way.

I apologize for the convoluted functions used, but AFAIK they are needed to deal with a database that was not designed well in the first place. Thanks in advance for any assistance you can provide!

Errors:
  • On the line containing "@atc_count = COUNT(ccr_j.CallID)": Incorrect syntax near '='.
  • On the line containing ") ccr_a": Incorrect syntax near 'ccr_a'.

Here is the query with the local variable, which produces the errors:
Code:
DECLARE @atc_count int;

SELECT
	ocl.CallID
,	s.Firstname
,	s.Lastname
,	s.EmailID
,	ocl.RecvdDate
,	ocl.RecvdTime
,	ocl.CallDesc
,	ocl.CloseDesc
,	(SELECT
		@atc_count = COUNT(ccr_j.CallID)
	FROM
		Journal ccr_j
	/*  JOIN with only the assignment having the latest resolution datetime,
		otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
	LEFT JOIN
		(SELECT	
			a.CallID --Needed for JOINing with the Journal table results
		,	MAX( COALESCE( CAST( CONVERT(char(10), a.DateResolv) + ' '
				+ CONVERT(char(8), a.TimeResolv) As datetime ), '1922-07-21 00:00:00')
			) As ts  --Use a label so we can compare later in the WHERE statement
		FROM Asgnmnt a
		GROUP BY a.CallID
		) ccr_a
	ON
		ccr_a.CallID = ccr_j.CallID
	WHERE
		ccr_j.CallID = ocl.CallID
		AND	ccr_j.JournalType = 'Ask to close'
		AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime ) > ts
	)
FROM
	CallLog ocl
LEFT JOIN
	Subset s
	ON s.CallID = ocl.CallID
WHERE
	ocl.Tracker = 'bharmo'
	AND ocl.CallStatus = 'Resolved'
	AND ocl.CallID IN
	(SELECT
		cl.CallID
	FROM
		CallLog cl
	LEFT JOIN
		Journal j
		ON j.CallID = cl.CallID
		AND j.JournalType = 'Ask to close'
	GROUP BY cl.CallID
	HAVING
		--Count only weekdays.
		(DateDiff( dd, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) -
		DateDiff( ww, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) * 2) >= 3
		AND @atc_count > 0
		AND @atc_count < 3
	)
ORDER BY
	ocl.CallID ASC


Here is the query where I remove the local variable from the SELECT, and replace it in the HAVING clause with a repeat of the full subquery, and it works:
Code:
DECLARE @atc_count int;

SELECT
	ocl.CallID
,	s.Firstname
,	s.Lastname
,	s.EmailID
,	ocl.RecvdDate
,	ocl.RecvdTime
,	ocl.CallDesc
,	ocl.CloseDesc
,	(SELECT
		COUNT(ccr_j.CallID)
	FROM
		Journal ccr_j
	/*  JOIN with only the assignment having the latest resolution datetime,
		otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
	LEFT JOIN
		(SELECT	
			a.CallID --Needed for JOINing with the Journal table results
		,	MAX( COALESCE( CAST( CONVERT(char(10), a.DateResolv) + ' '
				+ CONVERT(char(8), a.TimeResolv) As datetime ), '1922-07-21 00:00:00')
			) As ts  --Use a label so we can compare later in the WHERE statement
		FROM Asgnmnt a
		GROUP BY a.CallID
		) ccr_a
	ON
		ccr_a.CallID = ccr_j.CallID
	WHERE
		ccr_j.CallID = ocl.CallID
		AND	ccr_j.JournalType = 'Ask to close'
		AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime ) > ts
	) As NumRequests
FROM
	CallLog ocl
LEFT JOIN
	Subset s
	ON s.CallID = ocl.CallID
WHERE
	ocl.Tracker = 'bharmo'
	AND ocl.CallStatus = 'Resolved'
	AND ocl.CallID IN
	(SELECT
		cl.CallID
	FROM
		CallLog cl
	LEFT JOIN
		Journal j
		ON j.CallID = cl.CallID
		AND j.JournalType = 'Ask to close'
	GROUP BY cl.CallID
	HAVING
		--Count only weekdays.
		(DateDiff( dd, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) -
		DateDiff( ww, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) * 2) >= 3
		AND
		(SELECT
			COUNT(ccr_j.CallID)
		FROM
			Journal ccr_j
		/*  JOIN with only the assignment having the latest resolution datetime,
			otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
		LEFT JOIN
			(SELECT	
				a.CallID --Needed for JOINing with the Journal table results
			,	MAX( COALESCE( CAST( CONVERT(char(10), a.DateResolv) + ' '
					+ CONVERT(char(8), a.TimeResolv) As datetime ), '1922-07-21 00:00:00')
				) As ts  --Use a label so we can compare later in the WHERE statement
			FROM Asgnmnt a
			GROUP BY a.CallID
			) ccr_a
		ON
			ccr_a.CallID = ccr_j.CallID
		WHERE
			ccr_j.CallID = ocl.CallID
			AND	ccr_j.JournalType = 'Ask to close'
			AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime ) > ts
		) > 0
		AND
		(SELECT
			COUNT(ccr_j.CallID)
		FROM
			Journal ccr_j
		/*  JOIN with only the assignment having the latest resolution datetime,
			otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
		LEFT JOIN
			(SELECT	
				a.CallID --Needed for JOINing with the Journal table results
			,	MAX( COALESCE( CAST( CONVERT(char(10), a.DateResolv) + ' '
					+ CONVERT(char(8), a.TimeResolv) As datetime ), '1922-07-21 00:00:00')
				) As ts  --Use a label so we can compare later in the WHERE statement
			FROM Asgnmnt a
			GROUP BY a.CallID
			) ccr_a
		ON
			ccr_a.CallID = ccr_j.CallID
		WHERE
			ccr_j.CallID = ocl.CallID
			AND	ccr_j.JournalType = 'Ask to close'
			AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime ) > ts
		) < 3
	)
ORDER BY
	ocl.CallID ASC
Reply With Quote
  #2  
Old September 30th, 2012, 06:47 AM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Regular (2000 - 2499 posts)
  
Join Date: Jan 2003
Location: Paris Uppland
Posts: 2,352 swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level)swampBoogie User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 4 Days 9 h 55 m 42 sec
Reputation Power: 390
You cannot mix assignment, i.e.

Code:
SELECT
		@atc_count = COUNT(ccr_j.CallID)
	FROM


and retrieval in the same query.
Reply With Quote
  #3  
Old October 11th, 2012, 11:22 AM
Grimey Grimey is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2011
Posts: 14 Grimey User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 h 25 m 27 sec
Reputation Power: 0
Solution
I see. I certainly appreciate the help swampBoogie. I found a better way to do this, and frankly I'm not sure why I didn't see this in the first place. It's only lately that I've started to realize the utility of using a SELECT in a JOIN to include information like this. I guess it's still taking some time to sink in.

I took the @atc_count variable out altogether, and tacked on the counting subquery to the top level JOINs. It works like this and the query looks better, but seems to incur a noticeable performance hit.

Code:
SELECT
	ocl.CallID
,	s.Firstname
,	s.Lastname
,	s.EmailID
,	ocl.RecvdDate
,	ocl.RecvdTime
,	ocl.CallDesc
,	ocl.CloseDesc
,	atc.num_requests
FROM
	CallLog ocl
LEFT JOIN
	Subset s
	ON s.CallID = ocl.CallID
LEFT JOIN
	(SELECT			--Count how many current 'Ask to close' journal records for the ticket
		ccr_j.CallID
	,	COUNT(ccr_j.CallID) As num_requests
	FROM
		Journal ccr_j
	/*  JOIN with only the assignment having the latest resolution datetime,
		otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
	LEFT JOIN
		(SELECT	
			a.CallID --Needed for JOINing with the Journal table results
		,	MAX( CAST( CONVERT(char(10), a.DateResolv) + ' '
				+ CONVERT(char(8), a.TimeResolv) As datetime )) As ts  --Use a label so we can compare later in the WHERE statement
		FROM Asgnmnt a
		GROUP BY a.CallID
		) ccr_a
	ON
		ccr_a.CallID = ccr_j.CallID
	WHERE
		ccr_j.JournalType = 'Ask to close'
		AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime )
			> 
			CAST( COALESCE(ccr_a.ts, '1922-07-21 00:00:00') As datetime )  --Must COALESCE here, not in the JOIN above
	GROUP BY ccr_j.CallID
	) As atc
ON
	atc.CallID = ocl.CallID
WHERE
	ocl.Tracker = 'agifford'
	AND ocl.CallStatus = 'Resolved'
	AND ocl.CallID IN
	(SELECT
		cl.CallID
	FROM
		CallLog cl
	LEFT JOIN
		Journal j
		ON j.CallID = cl.CallID
		AND j.JournalType = 'Ask to close'
	GROUP BY cl.CallID
	HAVING
		--Count only weekdays.
		(DateDiff( dd, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) -
		DateDiff( ww, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) * 2) >= 3
		AND atc.num_requests > 0
		AND atc.num_requests < 3
	)
ORDER BY
	ocl.CallID ASC


This happens because now the counting subquery is not correlated, and is therefore looking at a whole lot of records which are irrelevant. So I added a JOIN to the CallLog table, which allows me to cull my search considerably. Now the query runs about 10 times as fast, and about 3 times quicker than the original with correlated subqueries.

Final result:

Code:
SELECT
	ocl.CallID
,	s.Firstname
,	s.Lastname
,	s.EmailID
,	ocl.RecvdDate
,	ocl.RecvdTime
,	ocl.CallDesc
,	ocl.CloseDesc
,	atc.num_requests
FROM
	CallLog ocl
LEFT JOIN
	Subset s
	ON s.CallID = ocl.CallID
LEFT JOIN
	(SELECT			--Count how many current 'Ask to close' journal records for the ticket
		ccr_j.CallID
	,	COUNT(ccr_j.CallID) As num_requests
	FROM
		CallLog cl  --Not required, but included to speed up the query by eliminating irrelevant Journal and Asgnmnt records
	LEFT JOIN
		Journal ccr_j
	ON
		ccr_j.CallID = cl.CallID
		AND cl.Tracker = 'agifford'  --Only look at tickets for which the user is the tracker
		AND cl.CallStatus = 'Resolved'  --Only look at resolved tickets
	/*  JOIN with only the assignment having the latest resolution datetime,
		otherwise we get duplicate CallIDs where >1 Asgnmnt record exists. */
	LEFT JOIN
		(SELECT	
			a.CallID --Needed for JOINing with the Journal table results
		,	MAX( CAST( CONVERT(char(10), a.DateResolv) + ' '
				+ CONVERT(char(8), a.TimeResolv) As datetime )) As ts  --Use a label so we can compare later in the WHERE statement
		FROM Asgnmnt a
		GROUP BY a.CallID
		) ccr_a
	ON
		ccr_a.CallID = ccr_j.CallID
	WHERE
		ccr_j.JournalType = 'Ask to close'
		AND	CAST( CONVERT(char(10), ccr_j.EntryDate) + ' ' + CONVERT(char(8), ccr_j.EntryTime) As datetime )
			> 
			CAST( COALESCE(ccr_a.ts, '1922-07-21 00:00:00') As datetime )  --Must COALESCE here, not in the JOIN above
	GROUP BY ccr_j.CallID
	) As atc
ON
	atc.CallID = ocl.CallID
WHERE
	ocl.Tracker = 'agifford'
	AND ocl.CallStatus = 'Resolved'
	AND ocl.CallID IN
	(SELECT
		cl.CallID
	FROM
		CallLog cl
	LEFT JOIN
		Journal j
		ON j.CallID = cl.CallID
		AND j.JournalType = 'Ask to close'
	GROUP BY cl.CallID
	HAVING
		--Count only weekdays.
		(DateDiff( dd, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) -
		DateDiff( ww, MAX( CAST( CONVERT(char(10), j.EntryDate) + ' ' + CONVERT(char(8), j.EntryTime) As datetime ) ), GETDATE() ) * 2) >= 3
		AND atc.num_requests > 0
		AND atc.num_requests < 3
	)
ORDER BY
	ocl.CallID ASC
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > SELECTing into local var, incorrect syntax near =

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap