-3

I am not so into Microsoft SQL Server and I have the following problem.

I have this query:

SELECT 
     [ID]
    ,[Numero protocollo]
    ,[Anno]
    ,[IdUor]
    ,[Protocollista]
    ,[Protocollato]
    ,[avorato]
    ,[Errore]
    ,[CopiaConoscenza]
    ,[Inoltro]
    ,[DataProtocollo]
    ,GETDATE () AS [Today]
    ,ABS(DATEDIFF(DAY, GETDATE (), [DataProtocollo])) AS [NumberOfDays]
FROM 
    [PROT_INOLTRO]
WHERE 
    [PROT_INOLTRO].IdUor = 1
    AND [PROT_INOLTRO].Protocollista = 'i:0#.w|iwgroupnet\anobili'
    AND [NumberOfDays] < 15
ORDER BY 
    ID DESC

As you can see, I added 2 calculated columns: [Today] and [NumberOfDays].

As you can see I am filtering using 3 WHERE conditions.

My problem is that I need to filter using the [NumberOfDays] column that is a calculated column, and not a column defined directly in the PROT_INOLTRO table.

Executing this query I get the following error message:

Msg 207, Level 16, State 1, Line 23
Invalid column name 'NumberOfDays'

Why can't I use this calculated column in my WHERE condition? How can fix my query to obtain this behavior?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • Even though the referenced "duplicate" answer is a solution, sometimes it may be slower in term of performance. I would consider repeating the expression in the `WHERE` clause if you need performance. – The Impaler Jan 07 '19 at 15:25
  • @TheImpaler In the referenced duplicate, somebody mentioned that in a comment. : ) – Tab Alleman Jan 07 '19 at 15:42
  • @TabAlleman Yes, but it's not directly available on the accepted answer. Just wanted to point it out loud and clear. – The Impaler Jan 07 '19 at 15:44

1 Answers1

0

Use like this:

...
AND ABS(DATEDIFF(DAY, GETDATE (), [DataProtocollo]))<15
apomene
  • 14,282
  • 9
  • 46
  • 72
  • Ok but can't I define a new field and use it in the where condition? – AndreaNobili Jan 07 '19 at 15:24
  • 1
    @AndreaNobili: not on the same "level" - if you want this, you must define the calculated column in a CTE (Common Table Expression) or a subquery, in order to use it in your "main" query .... – marc_s Jan 07 '19 at 15:48