#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    14
    Rep 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    You cannot mix assignment, i.e.

    Code:
    SELECT
    		@atc_count = COUNT(ccr_j.CallID)
    	FROM
    and retrieval in the same query.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    14
    Rep 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

IMN logo majestic logo threadwatch logo seochat tools logo