CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ayman
Ayman

Reputation: 872

SQL Server CTE query syntax error

I get this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

from this query:

seq = row_number() over 
(
  partition by t.CustID
  order by t.InvoiceID, 
           t.Date,
           CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
 )

;

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID
            ORDER BY InvoiceID,
                DATE,
                CASE 
                    WHEN S_Type = 'Receipt Voucher'
                        THEN 1
                    ELSE 2
                    END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    c.Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
        AND '2016-01-01'
ORDER BY seq

I have tried to add select in-front of seq I get these errors:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "t.CustID" could not be bound.

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "t.InvoiceID" could not be bound.

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "t.Date" could not be bound.

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "t.S_Type" could not be bound.

Msg 4104, Level 16, State 1, Line 34
The multi-part identifier ".Debit" could not be bound.

Upvotes: 0

Views: 446

Answers (1)

grahamj42
grahamj42

Reputation: 2762

You have a fragment of code at the beginning of your request seq=... which is invalid. Your query can use the seq calculated in the cte.

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID
            ORDER BY InvoiceID,
            DATE,
            CASE 
                WHEN S_Type = 'Receipt Voucher'
                    THEN 1
                ELSE 2
                END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    c.Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
    AND '2016-01-01'

ORDER BY c.seq;

There was also a missing c before Debit.

With thanks to a_horse_with_no_name.

Upvotes: 1

Related Questions