0

I have this select statement in SQL Server Management Studio 2014:

   SELECT main.car_descr
          ,vari.engineCode
          ,count(*) over(partition by vari.engineCode) as quant
    FROM dbspareparts.dbo.stock as stock

    inner join fiord.dbo.store as main on stock.[id_store]=main.[id_store]

    where quant>2

    order by quant desc

when I execute, I get "invalid column name" on the istruction

where quant>2

Why and how can I get this work?

I need to use the count(*) over(...) statement, cause this is only a piece of another longer query.

Thanks!

joe
  • 35
  • 7

1 Answers1

1
select * from (SELECT main.car_descr
      ,vari.engineCode
      ,count(*) over(partition by vari.engineCode) as quant
FROM dbspareparts.dbo.stock as stock

inner join fiord.dbo.store as main on stock.[id_store]=main.[id_store]) as tbl

where quant>2

order by quant desc

the quant isn't bound as a variable until after your query returns, and so you'll need to wrap it in an inner query before you can filter by it.

Eric Yang
  • 2,678
  • 1
  • 12
  • 18
  • where can I find more cases like this, explained and solved? I need to study this cases, cause this isn't the first time I stumble in one of them and for me it's still not clear what I must consider computed or not at the end of a query. until now I've solved all these problem in the code, making variables and launch queries in multiple times, saving the results in some variables, then using them in my code, but here I'm forced to make all inside few queries, without code. – joe Feb 17 '20 at 19:19
  • I would probably do the following exercises: https://www.w3resource.com/sql-exercises/. That being said you actually bumped into the one problem that's inconsistent across different SQL Databases. I believe that mysql allows you to do what you wanted in your initial query. – Eric Yang Feb 17 '20 at 19:35