CODEWITHSUNDEEP
sql-server
Jerome Bravin
Jerome Bravin

Reputation: 66

trying to create view in sql server

Can somebody help me create a view for the following code?

It's showing error near order by

    USE [ERPV9]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[vwSupplierReport]
    AS
    SELECT
    CT.TransDate, 
    CASE WHEN DN.DocNo IS NOT NULL THEN DN.DocNo ELSE CT.SupplierReference                           END AS DocNo,
   TT.TransTypeName AS [Description],
   RP.ChequeNo,
   CASE WHEN CT.TransTypeID = 20 THEN (MAX(CT.TotalAmount + CT.TotalTax + CT.Freight)) ELSE 0 END AS Debit, 
   CASE WHEN CT.TransTypeID IN (21,22) THEN MAX(CT.TotalAmount + CT.TotalTax + CT.Freight) ELSE 0 END AS Credit,
   (SUM(MAX(CT.TotalAmount + CT.TotalTax + CT.Freight)) OVER (ORDER BY CT.TransDate, CT.SuppTransID)) AS Balance 
   FROM SupplierTransactions CT
   LEFT JOIN DocumentNos DN ON CT.CompanyID = DN.CompanyID AND CT.TransTypeID = DN.TransTypeID AND CT.TransNo = DN.TransNo
   INNER JOIN TransactionTypes TT ON CT.CompanyID = TT.CompanyID AND CT.TransTypeID = TT.TransTypeID
   LEFT JOIN ReceiptsAndPayments RP ON CT.CompanyID = RP.CompanyID AND CT.TransTypeID = RP.TransTypeID AND CT.TransNo = RP.TransNo
   WHERE CT.CompanyID = 'ccaa3e3f-4486-4465-b5a1-723f647ead17' AND SupplierCode = 'SR0001 ' AND TransDate >= '2013-3-26' AND TransDate < '2015-2-27'

Upvotes: 0

Views: 49

Answers (1)

Stephan
Stephan

Reputation: 6018

Okay, so you cannot have MAX functions in your CASE statements. MAX is an aggregate and needs a group by clause. Also OVER(ORDER BY columns) is not supported by SQL Server 2008. 

USE [ERPV9]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSupplierReport]
AS
SELECT
        CT.TransDate, 
        CASE    WHEN DN.DocNo IS NOT NULL 
                THEN DN.DocNo 
                ELSE CT.SupplierReference                           
        END AS DocNo,
        TT.TransTypeName AS [Description],
        RP.ChequeNo,
        CASE  WHEN CT.TransTypeID = 20 
              THEN CT.TotalAmount + CT.TotalTax + CT.Freight
              ELSE 0 
        END AS Debit, 
        CASE  WHEN CT.TransTypeID IN (21,22) 
              THEN CT.TotalAmount + CT.TotalTax + CT.Freight
              ELSE 0 
        END AS Credit
        --SUM(CT.TotalAmount + CT.TotalTax + CT.Freight) 
        --    OVER (ORDER BY CT.TransDate, CT.SuppTransID) AS Balance
FROM        SupplierTransactions CT
LEFT JOIN   DocumentNos DN 
            ON CT.CompanyID = DN.CompanyID 
            AND CT.TransTypeID = DN.TransTypeID 
            AND CT.TransNo = DN.TransNo
INNER JOIN TransactionTypes TT 
            ON CT.CompanyID = TT.CompanyID 
            AND CT.TransTypeID = TT.TransTypeID
LEFT JOIN   ReceiptsAndPayments RP 
            ON CT.CompanyID = RP.CompanyID 
            AND CT.TransTypeID = RP.TransTypeID 
            AND CT.TransNo = RP.TransNo
WHERE   CT.CompanyID = 'ccaa3e3f-4486-4465-b5a1-723f647ead17' 
        AND SupplierCode = 'SR0001 ' 
        AND TransDate >= '2013-3-26' 
        AND TransDate < '2015-2-27'

Upvotes: 1

Related Questions