SQL invalid column name in sql query

Question

I have this SQL query (for SQL Server)

SELECT job.id AS job_id, 

( SELECT STRING_AGG( ISNULL(goods_rif.rif_car, ''), ',')
       FROM goods_rif  WHERE job.id = goods_rif.job_id 
) AS goodsrifcar

FROM job
WHERE ( job.job_creation_date >= DATEADD( DAY, -10, GETDATE() ) )   
ORDER BY job.id DESC

which works as expected.. but if I add a query clause (AND goodsrifcar= 'xxx') I get: invalid column name goodsrifcar

    SELECT job.id AS job_id, 
    
    ( SELECT STRING_AGG( ISNULL(goods_rif.rif_car, ''), ',')
           FROM goods_rif  WHERE job.id = goods_rif.job_id 
    ) AS goodsrifcar
    
    FROM job
    AND goodsrifcar= 'xxx'
    WHERE ( job.job_creation_date >= DATEADD( DAY, -10, GETDATE() ) )   
    ORDER BY job.id DESC

p.s. the sub query (... SELECT STRING_AGG...) only performs a concatenation of values from the "goods_rif" table

Firstly, `AND goodsrifcar= 'xxx'` should be in where clause (`WHERE ( job.job_creation_date >= DATEADD( DAY, -10, GETDATE() ) ) AND goodsrifcar= 'xxx'`).. Secondly, WHERE don't know aliases (or not? don't actually remember, try it out), use HAVING -> `WHERE ( job.job_creation_date >= DATEADD( DAY, -10, GETDATE() ) ) HAVING goodsrifcar= 'xxx'` — InDevX, Apr 14 '23 at 16:28

score 0 · Answer 1 · answered Apr 14 '23 at 16:36

You can use cte to make your code more readable:

with cte as
(   SELECT job.id AS job_id, 

        ( SELECT STRING_AGG( ISNULL(goods_rif.rif_car, ''), ',')
            FROM goods_rif  WHERE job.id = goods_rif.job_id 
        ) AS goodsrifcar

    FROM job
    WHERE ( job.job_creation_date >= DATEADD( DAY, -10, GETDATE() ) )       
) select * from cte where goodsrifcar= 'xxx'
  ORDER BY id DESC

SQL invalid column name in sql query

1 Answers1