Syntax Error Where None Exists 1

[tigerjade]

I'm getting a syntax error on this query and no one here (including yours truly) can figure out what SQL is REALLY trying to say.

Here's the code:

declare @VarKey int
set @VarKey = 3

UPDATE Schema1.Table
SET Rate = gl.Rate
FROM dbo.RateView gl WHERE gl.RateId = (SELECT RateId FROM Schema2.Table BP INNER JOIN Schema2.Table2 RA ON BP.TableId = RA.TableId
INNER JOIN Schema1.Table VI ON RA.Table2NotId = VI.Table2NotId
WHERE BP.OtherKey = @CompanyId)
GROUP BY gl.Rate
HAVING MIN(DATEDIFF(ss, gl.rateDate, VI.OtherDate)) < 0

And when I test I get this innocuous error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'GROUP'.

When I comment out both the GROUP BY and HAVING lines, it is happy. 'Course, then it's useless. What rule am I violating?

Thanks!

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

There are a couple problems (one of them hidden).

Your having clause uses VI.OtherDate, but this column only exists in the subquery.

You're treating the entire subquery as though it will always return a scalar value (one piece of data). Unfortunately, it may be possible for the subquery to return multiple rows which would certainly cause a problem for this query.

My suggestion is a total re-write (which isn't as bad as it sounds). My suggestion is to explain (in words) what this query is supposed to do. Then, show some sample data and expected results.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tigerjade]

Okay, let's see. I added in one more condition (thanks for that catch) that ensures that it always returns one value (because of the hierarchy, it's one parent-one child, so there'll always be one RateId to come out of the query) and pulled the subquery into a 'regular' query, but it still hates me.

UPDATE Schema1.Table
SET Rate = gl.Rate
FROM Schema1.Table VI INNER JOIN Schema2.Table2 RA ON VI.Table2NotId = RA.Table2NotId INNER JOIN Schema2.Table BP ON RA.TableId = BP.TableId INNER JOIN dbo.RateView gl ON BP.RateId = gl.RateId WHERE gl.CharField COLLATE DATABASE_DEFAULT LIKE VI.CharField COLLATE DATABASE_DEFAULT AND BP.OtherKey = @VarKey
GROUP BY gl.Rate
HAVING MIN(DATEDIFF(ss, gl.rateDate, VI.OtherDate)) < 0

Here's what should happen: we have rate data in the dbo.RateView view that should be tied to the row in Schema1.Table based on its parentage (Schema2.Table2 is VI's parent, and Schema2.Table is Table2's parent). I need the data that has the most recent rateDate as compared to the VI's OtherDate field (plus, without that, there's a whole boatload of rates that can be pulled in).

If I take out the Update and select based on the data I want, it's perfectly happy. It's just when I ask it to update the database that it complains about anything after the WHERE clause.

SELECT gl.Rate FROM Schema2.Table BP INNER JOIN Schema2.Table2t RA ON BP.TableId = RA.TableId
INNER JOIN Schema1.Table VI ON RA.Table2NotId = VI.Table2NotId INNER JOIN dbo.RateView gl ON BP.RateId = gl.RateId
WHERE BP.OtherKey = @VarKey AND gl.CharField COLLATE DATABASE_DEFAULT = VI.CharField COLLATE DATABASE_DEFAULT
GROUP BY gl.Rate
HAVING MIN(DATEDIFF(ss, gl.rateDate, GetDate())) > 0

This returns one value, which is what it should. That makes me suspect I've violated some rule wrt the update statement, 'cause GROUP BY and HAVING are perfectly happy here.

It absolutely doesn't make sense to write this as a cursor, so I'm trying desperately to avoid that, but time is creeping up on me. You do NOT want to know how long I've been fighting with this query. :>

Thanks for your help!

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[tigerjade]

Okay, more googling (done by a co-worker with superior google fu to mine) shows that it is indeed the HAVING clause combined with the UPDATE that is giving SQL fits. I'll need to use a CTE or something.

Thanks!

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[markros]

You can not use UPDATE with HAVING clause - it is not supported there.

See this thread

http://forums.asp.net/p/1473670/3419547.aspx#3419547

describing the exact same issue.