mercredi 8 juillet 2015

Cannot query an alias table

I'm doing something like:

SELECT T1.NAME, T2.DATE
    FROM T1
INNER JOIN
(
    SELECT * FROM OTHERTABLE
) AS T2 ON T2.USERID = T1.USERID

Which works, but if I query the alias table, I get an error saying that T2 is an invalid object name.

Example:

SELECT 
T1.NAME, 
T2.DATE,
CASE
    WHEN EXISTS (SELECT TOP 1 1 FROM T2 WHERE T2.THISFIELD = T1.THISFIELD) THEN 'HELLO'
    ELSE 'BYE'
END AS COMMENT -- THIS ALSO FAILS
    FROM T1
INNER JOIN
(
    SELECT * FROM OTHERTABLE
) AS T2 ON T2.USERID = T1.USERID
WHERE (SELECT COUNT(*) FROM T2) > 0

I thought that's what I did, "create" T2. Is there any way I can use T2 like such ?

My goal is to scrape all the related data from OTHERTABLE once because I'll have many CASE in the SELECT clause depending whether data exists in T2 or not. I don't want to do EXISTS for every field since that'll launch a new query in a huge table everytime.

Aucun commentaire:

Enregistrer un commentaire