Postgres reference subquery in case statement

Question

I'm trying to reference a subquery to use the value in a case statement. But, inside the SELECT statement I cannot reference the next_creation as it simply complains that the column does not exist. I thought about wrapping the entire query in a CASE statement but the CASE statement would require me to run the query multiple times and that seems silly.

I also tried to do a CROSS JOIN with the subquery, but it complains about not being able to reference tg inside the subquery. Kernel error: ERROR: invalid reference to FROM-clause entry for table "tg" HINT: There is an entry for table "tg", but it cannot be referenced from this part of the query.

I also tried using a WITH but it basically has the same result as CROSS JOIN. So I even tried appending two WITH clauses with the first query being the same at the main OUTER query so the second with could use the values....I got some error.

Is there any way to reference the subquery like stick it in a variable to use while still allowing the subquery to utilize the tg from the outer query?

SELECT *, (
    SELECT
        tg2.created
    FROM tocom t
    LEFT JOIN tocom_g_assoc ON tocom_g_assoc.table_id = t.id AND tocom_g_assoc.tocom_g_id != tg.id
    LEFT JOIN tocom_g tg2 ON tg2.id = tocom_g_assoc.tocom_g_id
    WHERE t.id IN ( SELECT t_id FROM tocom_g_assoc WHERE tocom_g_assoc.tocom_g_id = tg.id )
        AND tg2.createdt > tg.created
        AND tg2.created IS NOT NULL
    ORDER BY tg2.created ASC
    LIMIT 1
) next_creation

/*
Here I want to do as CASE statement, but for argument sake,
a simple next_creation - tg.dissolved_at would do
*/

FROM wp p
LEFT JOIN tocom_g tg ON tg.id = p.tocom_g_id
WHERE tg.dissolved_at IS NOT NULL

Senica Gonzalez · Accepted Answer · 2013-11-09 02:53:07Z

1

I realized the issue.

So I left it as a select, returning the subquery. Then I wrapper the whole thing in another select and made that query the from statement. I could then do whatever I wanted with the returned values.

answered Nov 9, 2013 at 2:53

Senica Gonzalez

8,22017 gold badges71 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres reference subquery in case statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related