Thread: ERROR: missing FROM-clause entry for table

I am receiving this error for the query pasted below.
Is the LEFT JOIN on the table not enough?
What needs to happen here?
I am guess something to do with derived tables

http://pastie.org/10715876



--
View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of bigkev
> Sent: Mittwoch, 10. Februar 2016 08:11
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] ERROR: missing FROM-clause entry for table
>
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
>
> http://pastie.org/10715876

It would help to know for which table the clause entry is missing. I guess that the order of the joins is not correct:

left join generate_series(c.start_time, c.end_time, '2 weeks'::interval) f(fortnight) ON g.day=f.fortnight
LEFT JOIN call_schedule c on extract(dow from c.start_time) = extract(dow from g.day)  AND f.fortnight IS NOT NULL AND
g.dayBETWEEN 
c.start_time AND c.end_time

In the first line you use c, but this is declared on the following line.

Bye
Charles

>
>
>
> --
> View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 2/9/2016 11:11 PM, bigkev wrote:
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
>
> http://pastie.org/10715876
>

Your error is in the reference to c.start_time, c.end_time.  During the
parse, the system doesn't know about "c" yet.
and swapping fortnight and "c" won't help - you can't reference
c.start_time in the "from" portion of the join.

So - substituting static values for c.start_time, c.end_time :
select * FROM generate_series('2016-01-22', '2017-12-31', '1
day'::interval) g(day)
left join generate_series('2015-01-25', '2016-07-01', '2
weeks'::interval) f(fortnight)  ON g.day=f.fortnight

generates results... but I'm not sure it is giving you what you want.

Exactly what are you trying to achieve with the fortnight construct?
BTW - assuming call_schedule.start_time is a timestamp... do your
start/end times cross day boundaries?  the test g.day between start/end
will never be true otherwise - you are dealing with "midnight" values
for time.
e.g. '2016-01-23' does not fall between '2016-01-23 08:30:01' and
"2016-01-23 10:45:01'

Roxanne

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

Yes, c.start_time is a timestamp.
My goal is to list events (calls) which are scheduled on fortnightly basis.

The query works with hard coded values, but I need to generate results for
days beginning at the start_time and ending with the end_time.: eg:

2016-02-06
2016-02-07
2016-02-08 | Some account name | other info
2016-02-09
2016-02-10
....
2016-02-22 | Some account name | other info
2016-02-23
2016-02-24
etc etc

All dates within the first generate_series are listed, 1 row for each day.
The second generate_series is to show events(calls) which begin on
c.start_time and end at c.start_time.
Records would be displayed, at each 14 days from the start_time.
In the above example, this would be 2016-02-08 and end_time would be a date
in the future.



--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5886777.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 2/10/2016 1:33 AM, bigkev wrote:
> Yes, c.start_time is a timestamp.
It is really late where I am, so I apologize if I'm being dense...
> My goal is to list events (calls) which are scheduled on fortnightly basis.

> The query works with hard coded values, but I need to generate results for
> days beginning at the start_time and ending with the end_time.: eg:
>
> 2016-02-06
> 2016-02-07
> 2016-02-08 | Some account name | other info
> 2016-02-09
> 2016-02-10
> ....
> 2016-02-22 | Some account name | other info
> 2016-02-23
> 2016-02-24
> etc etc
Is this exactly the format of the data you are looking for?
If there was an event on 02/10, would it show or not? [because it
doesn't fit in a fortnight schedule from 02/08, but could still be
scheduled to run fortnightly [e.g. 02/10, 02/24...]
> All dates within the first generate_series are listed, 1 row for each day.
> The second generate_series is to show events(calls) which begin on
> c.start_time and end at c.start_time.
> Records would be displayed, at each 14 days from the start_time.
I am confused about "which" start time.  Your description implies there
is just one start time that bounds your entire query.  You have a column
"start_time" in the Call Event table which is presumably the start of an
event.  However, each row in the table potentially has a totally
different value.  So what do you mean by "the start time"?  There isn't
a 14 days from "a" start time if you are using c.start_time and
calculating 14 days into the future for each row in the Call Event
table..  Each event would then have a fortnight period and your query
results are not bounded by time at all.

> In the above example, this would be 2016-02-08 and end_time would be a date
> in the future.
So for "the start time" do you mean the first (minimum) start time in
the Call Event table?
Or are you trying to gather all the call events that are scheduled 14
days apart, listed in order by day?
Is there anything that ties the call events together as being in a set
other than they take place exactly 14 days apart?

Roxanne

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

The call_schedule table looks like this
 id                | integer                     | not null default
nextval('call_schedule_id_seq'::regclass)
 account_id        | integer                     | not null
 user_id           | integer                     | not null
 call_type_id      | integer                     | not null
 call_frequency_id | integer                     | not null
 start_time        | timestamp without time zone | not null
 duration          | integer                     | not null
 end_time          | timestamp without time zone | not null
Indexes:
    "call_schedule_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "call_schedule_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
accounts(id)
    "call_schedule_call_frequency_id_fkey" FOREIGN KEY (call_frequency_id)
REFERENCES call_frequency(id)
    "call_schedule_call_type_id_fkey" FOREIGN KEY (call_type_id) REFERENCES
call_types(id)
    "call_schedule_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

This stores the start_time, which is the date and time a series of calls
begins. The duration defines how long this event(call) will go for, and the
end_time defines when the schedule will stop.
I need to generate a calendar eg: 1 year with intervals of 1 day, and
include fortnightly calls at the appropriate dates.
the call_frequency_id would be 5 for a fortnightly call.

I an post schema if you need.




--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5886990.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

...
This stores the start_time, which is the date and time a series of calls
begins. The duration defines how long this event(call) will go for, and the
end_time defines when the schedule will stop. 
I need to generate a calendar eg: 1 year with intervals of 1 day, and
include fortnightly calls at the appropriate dates. 
the call_frequency_id would be 5 for a fortnightly call.

I an post schema if you need.

If I've read your original query correctly.. Based on this ... I think you were just off a little bit on your idea.

Consider moving the fortnight generation into a sub-query join with the call_schedule... e.g.

SELECT g.*, c.meeting, a.name AS account_name, u.name AS user_name, c.start_time,
             c.start_time::timestamp+c.duration * '1s'::interval AS end_time,
             ct.name, extract(dow from c.start_time) AS start_day
FROM generate_series('2016-01-22', '2017-12-31', '1 day'::interval) g(day)
LEFT JOIN (
    select *, generate_series(c.start_time, c.end_time, '2 week'::interval) meeting
    from call_schedule c
    where call_frequency_id = 5
) c on ((g.day, '1 day'::interval) OVERLAPS (meeting, c.duration * '1s'::interval))
LEFT JOIN users u ON c.user_id=u.id
LEFT JOIN accounts a ON c.account_id=a.id
LEFT JOIN call_types ct ON c.call_type_id=ct.id
ORDER BY g.day

The generate_series in the sub-query could be genericized to deal with any frequency
through the use of a user defined function or a join on  your call_frequency table if it stores "interval" data.

Tuning wise, you may need to move the 3 dependent joins into the sub-query for better performance.
I'm not exactly sure what the sub-query does to the planner for index usage for joins...

I am also not sure what the performance difference might be between using OVERLAPS and BETWEEN.

Roxanne

-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

Yep, that nailed it.
Thanks for you time and help.



--
View this message in context:
http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5887183.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.