Обсуждение: nested query problem
Hi:
I'm having trouble with this query...
select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
from
sqf_runs sr,
(select perl_sub_name, end_datetime from flow_step_events_view where sqf_id = sr.sqf_id order by 2 limit 1) fse
where sr.userid='foo';
ERROR: invalid reference to FROM-clause entry for table "sr"
LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
^
HINT: There is an entry for table "sr", but it cannot be referenced from this part of the query.
If this is indeed a foul, how can I accomplish the same thing ?
Thanks in Advance !
On 09/06/2018 01:59 PM, David Gauthier wrote:
> I'm having trouble with this query...
>
> select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
> from
> sqf_runs sr,
> (select perl_sub_name, end_datetime from flow_step_events_view
> where sqf_id = sr.sqf_id order by 2 limit 1) fse
> where sr.userid='foo';
>
> ERROR: invalid reference to FROM-clause entry for table "sr"
> LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
> ^
> HINT: There is an entry for table "sr", but it cannot be referenced
> from this part of the query.
This calls for a lateral join:
SELECT sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
FROM sqf_runs sr
LEFT OUTER JOIN LATERAL (
SELECT perl_sub_name, end_datetime
FROM flow_step_events_view fsev
WHERE fsev.sqf_id = sr.sqf_id
ORDER BY 2
LIMIT 1
) fse
ON true
WHERE sr.userid = 'foo'
;
It's nearly what you had already, but `LATERAL` lets the subquery
reference columns in the other tables.
A lateral join is conceptually a lot like running your subquery in for
loop, looping over all the rows produced by the rest of the query. It
doesn't have to produce 1 row for each iteration, but saying `LIMIT 1`
ensures that here.
The `ON true` is just pro forma because you can't have a join without an
`ON` clause.
You might prefer an INNER JOIN LATERAL, depending on your needs.
--
Paul ~{:-)
pj@illuminatedcomputing.com
David Gauthier <davegauthierpg@gmail.com> writes:
> I'm having trouble with this query...
> select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
> from
> sqf_runs sr,
> (select perl_sub_name, end_datetime from flow_step_events_view where
> sqf_id = sr.sqf_id order by 2 limit 1) fse
> where sr.userid='foo';
> ERROR: invalid reference to FROM-clause entry for table "sr"
> LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
> ^
> HINT: There is an entry for table "sr", but it cannot be referenced from
> this part of the query.
If you actually meant to access the outer "sqf_runs" RTE from the
sub-query, you need to mark it LATERAL:
select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
from
sqf_runs sr,
lateral (select perl_sub_name, end_datetime from flow_step_events_view where
sqf_id = sr.sqf_id order by 2 limit 1) fse
where sr.userid='foo';
regards, tom lane
Not quite. This returns one value. In the actual "sqf_runs" table, there are many records with user_id = 'foo'. I want one line for each where the fse.p-erl_sub_name and fse.end_datetime values are the latest values found in the flow_step_events_view view where the sqf_ids match.
On Thu, Sep 6, 2018 at 5:10 PM Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
On 09/06/2018 01:59 PM, David Gauthier wrote:
> I'm having trouble with this query...
>
> select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
> from
> sqf_runs sr,
> (select perl_sub_name, end_datetime from flow_step_events_view
> where sqf_id = sr.sqf_id order by 2 limit 1) fse
> where sr.userid='foo';
>
> ERROR: invalid reference to FROM-clause entry for table "sr"
> LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
> ^
> HINT: There is an entry for table "sr", but it cannot be referenced
> from this part of the query.
This calls for a lateral join:
SELECT sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
FROM sqf_runs sr
LEFT OUTER JOIN LATERAL (
SELECT perl_sub_name, end_datetime
FROM flow_step_events_view fsev
WHERE fsev.sqf_id = sr.sqf_id
ORDER BY 2
LIMIT 1
) fse
ON true
WHERE sr.userid = 'foo'
;
It's nearly what you had already, but `LATERAL` lets the subquery
reference columns in the other tables.
A lateral join is conceptually a lot like running your subquery in for
loop, looping over all the rows produced by the rest of the query. It
doesn't have to produce 1 row for each iteration, but saying `LIMIT 1`
ensures that here.
The `ON true` is just pro forma because you can't have a join without an
`ON` clause.
You might prefer an INNER JOIN LATERAL, depending on your needs.
--
Paul ~{:-)
pj@illuminatedcomputing.com
Wow, I take that back. I thought there were many recs with "foo" but there wa sonly one.
When I ran this against a value that actually had multiple records, it ran fine.
When I ran this against a value that actually had multiple records, it ran fine.
Sorry for that.
And Thanks for this query !
On Thu, Sep 6, 2018 at 5:15 PM David Gauthier <davegauthierpg@gmail.com> wrote:
Not quite. This returns one value. In the actual "sqf_runs" table, there are many records with user_id = 'foo'. I want one line for each where the fse.p-erl_sub_name and fse.end_datetime values are the latest values found in the flow_step_events_view view where the sqf_ids match.On Thu, Sep 6, 2018 at 5:10 PM Paul Jungwirth <pj@illuminatedcomputing.com> wrote:On 09/06/2018 01:59 PM, David Gauthier wrote:
> I'm having trouble with this query...
>
> select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
> from
> sqf_runs sr,
> (select perl_sub_name, end_datetime from flow_step_events_view
> where sqf_id = sr.sqf_id order by 2 limit 1) fse
> where sr.userid='foo';
>
> ERROR: invalid reference to FROM-clause entry for table "sr"
> LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
> ^
> HINT: There is an entry for table "sr", but it cannot be referenced
> from this part of the query.
This calls for a lateral join:
SELECT sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
FROM sqf_runs sr
LEFT OUTER JOIN LATERAL (
SELECT perl_sub_name, end_datetime
FROM flow_step_events_view fsev
WHERE fsev.sqf_id = sr.sqf_id
ORDER BY 2
LIMIT 1
) fse
ON true
WHERE sr.userid = 'foo'
;
It's nearly what you had already, but `LATERAL` lets the subquery
reference columns in the other tables.
A lateral join is conceptually a lot like running your subquery in for
loop, looping over all the rows produced by the rest of the query. It
doesn't have to produce 1 row for each iteration, but saying `LIMIT 1`
ensures that here.
The `ON true` is just pro forma because you can't have a join without an
`ON` clause.
You might prefer an INNER JOIN LATERAL, depending on your needs.
--
Paul ~{:-)
pj@illuminatedcomputing.com