Re: Learning SQL: nested CTE and UNION

Thanks for your reply. I think I should have been clearer. I'm trying to understand why the error message says "relation "innermost" does not exist" when clearly it does (seem to). Let me try asking using different examples:

I can UNIONize two selections that refer to the same CTE, like this:

WITH foo AS (SELECT 1 AS mycol) SELECT mycol FROM foo UNION SELECT mycol+1 FROM foo;

and I don't need to put everything before the UNION inside parentheses.

The change you made to the query I posted is to move a parenthesis so that the second part of the union cannot refer to the CTE the way the line above does.

Put another way, here is the non-working query that I posted in my last message, with one minor change to reference a column in the purportedly nonexistent relation:

WITH outmost AS (
SELECT 1
UNION (WITH innermost as (SELECT 2 as mycol)
SELECT mycol FROM innermost
UNION SELECT mycol+1 from innermost)
)
SELECT * FROM outmost;

Can you make this one work by moving a parenthesis?

Moreover, removing the "SELECT 1 UNION" starting on the second line will make the error message about "innermost" not existing go away, which does not make sense to me. There seems to be some strange (to me) interaction between the CTEs and UNION. If this is something you understand I would most gratefully appreciate any explanation or guidance that could help me to understand.

Note, I'm not trying to just get something to happen, I'm trying to understand why I am getting a message telling me that "innermost" does not exist, when it looks to me as if it does exist.

Thank you.

On Tue, Jul 31, 2012 at 9:57 AM, Jonatan Reiners <jreiners(at)encc(dot)de> wrote:
>
> I hope this gives you a clue.
>
> This works:
>
> WITH outmost AS (
> SELECT 1
> UNION (WITH innermost as (SELECT 2)
> SELECT * FROM innermost
> )UNION SELECT 3
> )
> SELECT * FROM outmost;
>
> But this does not work:
>
> WITH outmost AS (
> SELECT 1
> UNION (WITH innermost as (SELECT 2)
> SELECT * FROM innermost
> UNION SELECT 3)
> )
> SELECT * FROM outmost;
>
>
> --
> Jonatan Reiners
>

--
Adam Mackler