Are you using rname feature? If so, the issue may need to be further investigate as why SELECT DISTINCT ON (TABLE_of_sites.title) is not double quoted and all the other table_name and field instance are...
So maybe the flaw is somewhere in the escaping related to rname feature... Richard On Thu, Jul 13, 2017 at 2:11 PM, lyn2py <lyn...@gmail.com> wrote: > Following this lead https://stackoverflow.com/questions/34708963/error- > missing-from-clause-entry-for-table > > I discovered that if we add double quotes to the tablename, for all > instances of the tablename, like this: > "TABLE_of_sites" > > Then the query will work. > > > On Friday, July 14, 2017 at 1:48:10 AM UTC+8, lyn2py wrote: >> >> Table code: >> db.define_table('TABLE_of_sites', >> Field('site_id', 'reference sites'), >> Field('title', 'string'), >> Field('date','datetime'), >> ... >> Field('updated','datetime', update=request.now, writable=False), >> format='%(site_id)s' >> ) >> >> >> In controller: >> table = db['TABLE_of_sites'] >> rows = db(table).select(distinct=table.title) >> >> Gives the error: >> >> ProgrammingError: missing FROM-clause entry for table "table_of_sites" >> LINE 1: SELECT DISTINCT ON (TABLE_of_sites.title) "TABLE_of... >> >> ^ >> >> >> I printed the sql query that resulted from the above code. It has the >> FROM-clause, so I don't know why it would give the error. This is the >> printed query: >> SELECT DISTINCT ON (TABLE_of_sites.title) "TABLE_of_sites"."id", >> "TABLE_of_sites"."site_id", "TABLE_of_sites"."date", >> "TABLE_of_sites"."title" FROM "TABLE_of_sites" WHERE ("TABLE_of_sites"."id" >> IS NOT NULL); >> >> I need to select 1 row for entries that appear multiple times for a >> particular title, hence I'm using distinct (or aggregates like max() - >> details in my previous post) but both are not working. >> >> Thank you for your time and assistance! >> >> >> >> >> On Tuesday, July 11, 2017 at 5:40:08 AM UTC+8, Massimo Di Pierro wrote: >>> >>> It took a year. Thanks to everybody who contributed. There are a ton of >>> bug fixes and speed improvements. >>> >>> I would encourage everybody who contributed to add a comment in this >>> thread explaining the main features/improvements they have made. >>> >>> If anything does not work or was broken it is only fault and I will >>> remedy if reported. >>> >>> Massimo >>> >> -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
