0

I am trying to figure out why the following Microsoft SQL code does not work. I simplified the query as it is quite complex. Basically the part that is not working is the second nested subquery (line FROM a) - I get an error: Invalid object name 'a'.

I would appreciate any advice on why it is not working and how I could make it work. Some background sources on why is it not working would also be helpful, as I struggle to find any information on limitations of nested queries beyond some basics.

SELECT * 
FROM (
SELECT ... FROM ...
) a

WHERE x IN( 
SELECT x 
FROM a  
WHERE v1=v2)
joanxm
  • 1
  • Perhaps you want a cte? – jarlh Nov 18 '20 at 14:04
  • It appears at though the error would be resulting from the second to last line in your pseudo query as the `a` in question is an alias for the first subquery. Can you provide any sample date and code so working alongside to debug would be more effective? – etch_45 Nov 18 '20 at 14:04
  • 1
    thanks @jarlh, I just looked up what ctes are and I managed to rewrite my code using cte so that it works. Unfortunately I am not able to provide sample date as the data I work on is private sensitive data. But the problem is solved, thanks! – joanxm Nov 18 '20 at 14:13
  • You have more information here: https://stackoverflow.com/questions/35248217/multiple-cte-in-single-query – Emin Mesic Nov 18 '20 at 19:21

1 Answers1

0

I managed to solve my problem thanks to the suggestion in the comments to use CTE. So I transformed it into:

WITH CTE_1
AS
 (
SELECT ... FROM ...
) 
SELECT * FROM CTE_1
WHERE x IN( 
SELECT x 
FROM CTE_1  
WHERE v1=v2)
joanxm
  • 1