nesting subqueries

[Bert]

Consider the following query:

SELECT
Projectid,
'fldValue_1' =
CASE
WHEN ISNULL(fldTotal_1,0) - ISNULL(fldTotal_2,0) < 0 THEN 0
ELSE ISNULL(fldTotal_1,0) - ISNULL(fldTotal_2,0)
END
FROM dbTable1 q1
LEFT JOIN
(SELECT ID, SUM(fldAmount_1) AS fldTotal_1 FROM dbTable2 GROUP BY ID) q2
ON q1.ID=q2.ID
LEFT JOIN
(SELECT ID, SUM(fldAmount_2) AS fldTotal_2 FROM dbTable3 GROUP BY ID) q3
ON q1.ID=q3.ID

This will work.
But if I want to nest it again into

SELECT Projectid, SUM(fldValue_1) FROM ( .....previous query ...... )
GROUP BY Projectid

I get a syntax error.
Am I violating a rule in SQL syntax ? (SQL-server)

 

[Sylvain Lafontaine]

You must give an alias to your subquery; try:

SELECT Projectid, SUM(fldValue_1) FROM ( .....previous query ...... ) as Q4
GROUP BY Projectid

 

[Bert]

Thanks Sylvain, that did the trick....

Bert

Op Tue, 16 Sep 2008 17:46:18 +0200 schreef Sylvain Lafontaine <sylvain aei