Incorrect syntax error on SQL server

Question

I am no SQL expert but I can't see why I'm getting an error with the following code: http://sqlfiddle.com/#!3/2de76/7. Please can someone tell me what is wrong with this. Thanks.

DECLARE @total INT
SET @total = 0
SELECT
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total = @total
        WHEN s1.dayTotal > s2.dayTotal      THEN @total = @total + (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

The expected output:

rowCount,res,dayTotal,dayTotal
1,3,3,0
2,4,4,3
3,4,3,4
4,7,6,3

Are you attempting to get a running total in the @Total variable? — user5151179
– user5151179, Commented Sep 18, 2015 at 8:30

Mihai Ovidiu Drăgoi · Accepted Answer · 2015-09-18 09:14:12Z

3

You're trying to use the CASE's THEN to alter the value of a variable. Doesn't work like that. This, however, works, and looks more close to what you might want to achieve:

DECLARE @total INT
SET @total = 0
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total
        WHEN s1.dayTotal > s2.dayTotal      THEN (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

PS - your CASE doesn't take the possibility of s1.dayTotal = s2.dayTotal into account. Not sure that's okay.

PPS - if you want the second WHEN to sum up the total AND the difference, use

WHEN s1.dayTotal > s2.dayTotal      THEN @total+(s1.dayTotal - s2.dayTotal)

PPPS - This seems to produce the desired resultset:

select a.RowCountID, case when b.diff > 0 then a.res+b.diff else a.res end as res, a.daytotal1, a.daytotal2  from
(
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
  ) a 
  left join
(
  SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
) b on a.rowcountID = b.rowcountID+1
order by a.rowcountID

Not sure what this kind of self-joining query is named, but it should have a name :)

Hope it works well!

edited Sep 18, 2015 at 9:14

answered Sep 18, 2015 at 8:27

Mihai Ovidiu Drăgoi

1,3171 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user743382 Over a year ago

I think this misses the point of the OP's use of a variable, which is to sum together the totals of different rows.

Ukuser32 Over a year ago

Sorry for a delay. What I would ultimately like is for res to be 3,4,4,7 - it must be a cumulative figure.

user743382 Over a year ago

Yeah, that's what I meant with "sum together the totals of different rows" too. This answer still doesn't do that even with the edit. @Ukuser32 I think it would be clearer to other users if you could include the sample data and the expected output from your comment directly in your question.

Ukuser32 Over a year ago

Done - I was just wanting the query to run not expecting us to fix it as well :)

Mihai Ovidiu Drăgoi Over a year ago

Could you explain the res column? what do you want it to contain, exactly?

|

Sushil · Accepted Answer · 2015-09-18 18:29:56Z

1

You need to remove "@total =" from the case statement.

DECLARE @total int
SET @total = 0

SELECT
  s1.RowCountID,
  CASE
    WHEN s1.dayTotal < s2.dayTotal THEN @total
    WHEN s1.dayTotal > s2.dayTotal THEN @total + (s1.dayTotal - s2.dayTotal)
  END AS res,
  s1.dayTotal,
  s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2
  ON s2.RowCountID = (s1.RowCountID - 1)
ORDER BY s1.RowCountID ASC

edited Sep 18, 2015 at 18:29

Sushil

2,8434 gold badges23 silver badges29 bronze badges

answered Sep 18, 2015 at 18:05

Somayeh Ghazvinian

1441 gold badge3 silver badges17 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:53:30Z

0

Ok after further investigation (and finding how to get cumulative sum), the following is the correct code to make it work (but with the addition of the case command):

SELECT
    s1.RowCountID,
      SUM(
      CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN 0
        WHEN s1.dayTotal > s2.dayTotal      THEN s1.dayTotal - s2.dayTotal
      END) OVER(
           ORDER BY s1.RowCountID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS res2,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

My actual issue is I also use MySQL (I was experimenting to see if it was possible elsewhere) so the next question is how this would look in MySQL?? That will take some further investigations!!

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 18, 2015 at 9:38

Ukuser32

2,1893 gold badges23 silver badges33 bronze badges

2 Comments

user743382 Over a year ago

I considered answering with this already, except you put "SQL Server 2008" in your question title and this is only supported starting with SQL Server 2012. (Note: it does work on SQLFiddle's "MS SQL Server 2008" mode, but that isn't actually a 2008 version.)

Ukuser32 Over a year ago

Ah! Maybe I should change the title. It was only because I was using SQL fiddle!!

Collectives™ on Stack Overflow

Incorrect syntax error on SQL server

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related