Error Column name does not exist in SQL Server

Question

Error Column name does not exist in SQL Server

Navigation

#1 by (1 votes)

2

I'm doing a query whose purpose is to put a label called Match and then filter that Match with WHERE , but it generates an error "Column name does not exist".

My script is as follows:

SELECT
    [Contra], [Schedule],
    SUM(CAST([UsedSM] AS float)) AS [UsedSM], SUM(CAST([TotalSU] AS int)) AS [TotalSU],
    SUM(CAST([UsedSU] AS float)) AS [UsedSU],
    MatchExpireDate = Case When FIRST_VALUE(Schedule) OVER (PARTITION BY [Contra] 
                                                            ORDER BY [Schedule] ASC
                                                            ROWS UNBOUNDED PRECEDING
                                                            ) = [Schedule] Then 'Match'
    Else 'Not' End

    FROM [dbMartxxx].[dbo].[tblPDPcontracts] where MatchExpireDate = 'Match'
    GROUP BY [Contra], [Schedule]
    ORDER BY [Contra]  DESC;
GO

sql sql-server

asked by Cesar Vanegas Castro 24.05.2018 в 17:27

source

1 answer

What is the behavior of ngfor and ngif? ERROR 500 when sending php form [closed]

score 1 · Answer 1

Unfortunately for how the queries are processed, in the instance of WHERE the column MatchExpireDate does not exist, hence the error. What you can do is:

Use a subquery

SELECT  *
    FROM (SELECT    [Contra],
            [Schedule],
            SUM(CAST([UsedSM] AS float)) AS [UsedSM],
            SUM(CAST([TotalSU] AS int)) AS [TotalSU],
            SUM(CAST([UsedSU] AS float)) AS [UsedSU],
            MatchExpireDate = Case When FIRST_VALUE(Schedule) OVER (PARTITION BY [Contra] 
                                                                       ORDER BY [Schedule] ASC
                                                                       ROWS UNBOUNDED PRECEDING
                                                                       ) = [Schedule] Then 'Match'
                                       Else 'Not' 
                     End

            FROM [dbMartxxx].[dbo].[tblPDPcontracts]
            GROUP BY [Contra],
                     [Schedule]
    ) T
    where T.MatchExpireDate = 'Match'
    ORDER BY T.Contra  DESC;