Hi all,
I'm pondering an issue which has arisen.
Suppose I have a query of the type:
SELECT
x.f1,
x.f2,
..
x.fn,
y.f1,
y.f2,
..
y.fn,
COUNT(z.id)
FROM xtab x
JOIN ytab y ON x.x_key = y.y_key
JOIN ztab z ON z.z_key = y._other_key
Now, what I would like to do is to put the COUNT(z.id) into a CTE and
then essentially SELECT a constant value into that part of the query
WITH cte AS
(
SELECT COUNT(z.id) AS zcnt FROM z
)
SELECT
x.fields,
y.fields,
c.zcnt
FROM xtab x JOIN ytab y ON x.x_key = y.y_key
JOIN cte c ON .......
And it's here that I'm stuck. I just can't figure out how to get the
constant value into the result set.
I keep getting messages like "unknown column 'p.pcnt'".
Any ideas, references &c appreciated!
TIA and g