locked
sql syntax error RRS feed

  • Question

  • User-172769993 posted

    Error i get

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

    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,
        .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

    Tuesday, January 12, 2016 5:23 PM

Answers

  • User2103319870 posted

    Error i get

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

    Query

     seq = row_number() over 

    Try adding a select infront of seq like below

     select 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,
        .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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2016 6:26 PM
  • User-62323503 posted

    what is first select statement for? It should not be there

    Try this only

    ;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,
        .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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 13, 2016 7:26 AM

All replies

  • User2103319870 posted

    Error i get

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

    Query

     seq = row_number() over 

    Try adding a select infront of seq like below

     select 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,
        .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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2016 6:26 PM
  • User-172769993 posted

    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.

    Tuesday, January 12, 2016 7:17 PM
  • User-62323503 posted

    what is first select statement for? It should not be there

    Try this only

    ;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,
        .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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 13, 2016 7:26 AM