infox09 infox09 - 4 months ago 11
SQL Question

Getting error in creation of view in SQL Server

I want to create view with T-SQL.

My query is:

CREATE VIEW [dbo].[MoyenPerMonth] (
MonthMoyen
,MonthReception
,YearReception
)
AS
SELECT AVG(DATEDIFF(day, DFC.DateReponse, DFC.DateReception))
,DATEPART(mm, DFC.DateReception)
,DATEPART(yyyy, DFC.DateReception)
FROM [DFC]
WHERE DFC.CompleteFlag = 1
AND DATEPART(mm, GETDATE()) = DATEPART(mm, DFC.DateReception)
AND DATEPART(yyyy, GETDATE()) = DATEPART(yyyy, DFC.DateReception)
GO


but it throws an error


Msg 8120, Level 16, State 1, Procedure MoyenPerMonth, Line 2

Column 'DFC.DateReception' is invalid in the select list because

it is not contained in either an aggregate function or the GROUP BY clause.

Answer

You have an aggregation function in the SELECT, but no GROUP BY. I assume you intend:

CREATE VIEW [dbo].[MoyenPerMonth] (MonthMoyen, MonthReception, YearReception) AS
    SELECT AVG(DATEDIFF(day, DFC.DateReponse, DFC.DateReception)),
           DATEPART(month, DFC.DateReception), DATEPART(year, DFC.DateReception)
    FROM [DFC]
    WHERE DFC.CompleteFlag = 1 AND
          DATEPART(month, GETDATE()) = DATEPART(month, DFC.DateReception) AND
          DATEPART(year, GETDATE()) = DATEPART(year, DFC.DateReception)
    GROUP BY DATEPART(month, DFC.DateReception), DATEPART(year, DFC.DateReception);

Alternatively, because you only seem to one one row in the result, you can use GETDATE() in the SELECT:

CREATE VIEW [dbo].[MoyenPerMonth] (MonthMoyen, MonthReception, YearReception) AS
    SELECT AVG(DATEDIFF(day, DFC.DateReponse, DFC.DateReception)),
           DATEPART(month, GETDATE()), DATEPART(year, GETDATE())
    FROM [DFC]
    WHERE DFC.CompleteFlag = 1 AND
          DATEPART(month, GETDATE()) = DATEPART(month, DFC.DateReception) AND
          DATEPART(year, GETDATE()) = DATEPART(year, DFC.DateReception);

This is allowed in the SELECT because the non-aggregated columns are constants.