Thread: Inserting a constant along with field values.

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

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.ie> writes:
> 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 .......

That looks fine as far as it goes.

> I keep getting messages like "unknown column 'p.pcnt'".

There's no p.pcnt in what you showed, so it seems like maybe your
problem is somewhere else.

If you're trying to figure out what the join condition should be:
you don't need one, because the single row from "cte c" is supposed
to join to every row of the x/y join result.  So you could just
write "ON true".  Or leave it off altogether by writing CROSS JOIN,
or by using comma syntax, like

... FROM xtab x JOIN ytab y ON x.x_key = y.y_key, cte c

            regards, tom lane

> If you're trying to figure out what the join condition should be:
> you don't need one, because the single row from "cte c" is supposed
> to join to every row of the x/y join result.  So you could just
> write "ON true".  Or leave it off altogether by writing CROSS JOIN,
> or by using comma syntax, like

> ... FROM xtab x JOIN ytab y ON x.x_key = y.y_key, cte c

That worked a treat, thanks Tom! I was wandering (lonely as a cloud
perhaps?) along that route, but I just couldn't get it to work. Maybe
I just needed the input of someone like yourself to convince me that I
was, in fact, on the right track.

Thanks again and rgs,

Pól...

>                         regards, tom lane