• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

can't find syntax error in stored procedure

This query runs perfectly in .net Query Builder but generates a syntax error in sql server. Syntax error near the keyword GROUP. It is referring to the last GROUP in the query. What am I missing?
SELECT     A.betId, A.userId, A.firstName, A.lastName, A.entryDate AS lastEntryDate, A.firstName + ' ' + LEFT(A.lastName, 1) AS nickName, A.weight, A.goalWeight, 
                      A.diff, B.firstEntryDate, B.startingWeight, - ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) AS percentGoal
FROM         (SELECT     _userBets.betId, wl.Id AS weightLogId, wl.userId, _users.firstName, _users.lastName, wl.entryDate, wl.weight, _userBets.goalWeight, 
                                              wl.weight - _userBets.goalWeight AS diff
                       FROM          (SELECT     userId, MAX(entryDate) AS lastEntryDate
                                               FROM          _weightLog
                                               GROUP BY userId) AS x INNER JOIN
                                              _weightLog AS wl ON wl.userId = x.userId AND wl.entryDate = x.lastEntryDate INNER JOIN
                                              _users ON wl.userId = _users.Id INNER JOIN
                                              _userBets ON _users.Id = _userBets.userId
                       WHERE      (_userBets.betId = @BetId)) AS A INNER JOIN
                          (SELECT     wl2.userId, y.firstEntryDate, wl2.weight AS startingWeight
                            FROM          (SELECT     userId, MIN(entryDate) AS firstEntryDate
                                                    FROM          (SELECT     _weightLog.entryDate, _weightLog.userId, _weightLog.weight, _userBets.goalWeight, _bets.startDate, 
                                                                                                   _userBets.betId
                                                                            FROM          _userBets INNER JOIN
                                                                                                   _bets ON _userBets.betId = _bets.Id INNER JOIN
                                                                                                   _weightLog ON _userBets.userId = _weightLog.userId AND _bets.startDate <= _weightLog.entryDate
                                                                            WHERE      (_userBets.betId = @BetId))
                                                    GROUP BY userId) AS y INNER JOIN
                                                   _weightLog AS wl2 ON wl2.userId = y.userId AND wl2.entryDate = y.firstEntryDate) AS B ON A.userId = B.userId
ORDER BY - ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) DESC

Open in new window

0
aorkand
Asked:
aorkand
1 Solution
 
matty1stopCommented:
I think you have an extra close paren on this line
(_userBets.betId = @BetId))

Should be
(_userBets.betId = @BetId)
0
 
Snarf0001Commented:
The extra parenth. should be there, it's closing one of the above sub-selects.
I think you missed aliasing the subselect right before the 2nd group by.  I named it as tempX in code, try this:
SELECT A.betId, A.userId, A.firstName, A.lastName, A.entryDate AS lastEntryDate, A.firstName + ' ' + LEFT(A.lastName, 1) AS nickName, A.weight, A.goalWeight, 
A.diff, B.firstEntryDate, B.startingWeight, - ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) AS percentGoal
FROM         
(
	SELECT _userBets.betId, wl.Id AS weightLogId, wl.userId, _users.firstName, _users.lastName, wl.entryDate, wl.weight, _userBets.goalWeight, wl.weight - _userBets.goalWeight AS diff
	FROM 
	(
		SELECT     userId, MAX(entryDate) AS lastEntryDate
		FROM          _weightLog
		GROUP BY userId
	) AS x 
	INNER JOIN _weightLog AS wl ON wl.userId = x.userId AND wl.entryDate = x.lastEntryDate 
	INNER JOIN _users ON wl.userId = _users.Id 
	INNER JOIN _userBets ON _users.Id = _userBets.userId
	WHERE _userBets.betId = @BetId
) AS A 
INNER JOIN
(
	SELECT wl2.userId, y.firstEntryDate, wl2.weight AS startingWeight
	FROM 
	(
		SELECT userId, MIN(entryDate) AS firstEntryDate
		FROM          
		(
			SELECT _weightLog.entryDate, _weightLog.userId, _weightLog.weight, _userBets.goalWeight, _bets.startDate, _userBets.betId
			FROM _userBets 
			INNER JOIN _bets ON _userBets.betId = _bets.Id 
			INNER JOIN _weightLog ON _userBets.userId = _weightLog.userId AND _bets.startDate <= _weightLog.entryDate
			WHERE _userBets.betId = @BetId
		) as tempX
		GROUP BY userId
	) AS y 
	INNER JOIN _weightLog AS wl2 ON wl2.userId = y.userId AND wl2.entryDate = y.firstEntryDate
) AS B ON A.userId = B.userId
ORDER BY - ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) DESC

Open in new window

0
 
Richard QuadlingSenior Software DeveloperCommented:
It seems adding an alias to the sub select allows the query to pass muster in query analyzer.

Sorry for the layout, but that's just how I can read it.
declare @BetID as integer
 
SELECT
	A.betId,
	A.userId,
	A.firstName,
	A.lastName,
	A.entryDate AS lastEntryDate,
	A.firstName + ' ' + LEFT(A.lastName, 1) AS nickName,
	A.weight,
	A.goalWeight,
	A.diff,
	B.firstEntryDate,
	B.startingWeight,
	- ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) AS percentGoal
FROM
	( -- Start of first sub-select
	SELECT
		_userBets.betId,
		wl.Id AS weightLogId,
		wl.userId,
		_users.firstName,
		_users.lastName,
		wl.entryDate,
		wl.weight,
		_userBets.goalWeight,
		wl.weight - _userBets.goalWeight AS diff
	FROM
		( -- Start of second sub-select
		SELECT
			userId,
			MAX(entryDate) AS lastEntryDate
		FROM
			_weightLog
		GROUP BY
			userId
 		) AS x INNER JOIN -- End of second sub-select
		_weightLog AS wl
			ON
				wl.userId = x.userId
				AND
				wl.entryDate = x.lastEntryDate
		INNER JOIN
                _users
			ON
				wl.userId = _users.Id
		INNER JOIN
                _userBets
			ON
				_users.Id = _userBets.userId
	WHERE
		(_userBets.betId = @BetId)
	) AS A -- End of first sub-select
	INNER JOIN
	( -- Start of third sub-select
	SELECT
		wl2.userId,
		y.firstEntryDate,
		wl2.weight AS startingWeight
	FROM
		( -- Start of fourth sub-select
		SELECT
			Z.userId,
			MIN(Z.entryDate) AS firstEntryDate
		FROM
			( -- Start of fifth sub-select
			SELECT
				_weightLog.entryDate,
				_weightLog.userId,
				_weightLog.weight,
				_userBets.goalWeight,
				_bets.startDate, 
				_userBets.betId
			FROM
				_userBets
				INNER JOIN
                                _bets
					ON
						_userBets.betId = _bets.Id
				INNER JOIN
				_weightLog
					ON
						_userBets.userId = _weightLog.userId
						AND
						_bets.startDate <= _weightLog.entryDate
			WHERE
				(_userBets.betId = @BetId)
			) AS Z-- End of fifth sub-select
		GROUP BY Z.userId
		) AS y -- End of fourth sub-select
		INNER JOIN
		_weightLog AS wl2
			ON
				wl2.userId = y.userId
				AND
				wl2.entryDate = y.firstEntryDate
	) AS B -- End of third sub-select
		ON
			A.userId = B.userId
ORDER BY
	- ((B.startingWeight - A.weight) / (A.goalWeight - B.startingWeight) * 100) DESC

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
fesnyngCommented:
LOL.  I am just too slow on the darw.  Just got the same answer as RQuadling:with a similar reformatting of the query.

As RQuadling says -- it needs an alias for the inline table just before the GROUP that Query Analyzer is complaining about.

...
      INNER JOIN _weightLog
      ON _userBets.userId = _weightLog.userId
      AND _bets.startDate <= _weightLog.entryDate
      WHERE (_userBets.betId = @BetId)) as ABC
  GROUP BY userId
) AS y  ...


0
 
aorkandAuthor Commented:
it was the ailiasing!! thanks!
0
 
aorkandAuthor Commented:
thanks to both RQuading and fesnyng! Aliasing did the trick
0
 
Richard QuadlingSenior Software DeveloperCommented:
I completely missed Snarf's answer when I started on this. Took me that long to reformat the query!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now