PATCH
File gluon/sql.py, line #2987 in _select method of Set (SQLSet) class
should be:
return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM
%s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f,
sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o)
Previoisly was:
return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM
%s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s;' % (sql_s, sql_f, sql_f, sql_t,
sql_w, sql_o, lmax, sql_t, sql_w_row)
This adds GROUP BY clause at the end of outer query and fixes error in
Oracle. This make possible queries with left join and grouping, e.g.
db(cond).select(db.carts.ALL, db.carts.id.count(),
left=db.c_h.on(many_to_many),
groupby=db.carts.ALL,
orderby=carts_orderby,
limitby = session.carts_limitby)
I tested this very extensively.
Another problem I reported in this post:
data = db(db.headers.id == db.alarms.header).select(
db.header.name, db.alarms.komment,
limitby=(0, 5)
)
To make it working in Oracle you should add all fields taking part in
WHERE clause to the list of fields to SELECT, i.e.
data = db(db.headers.id == db.alarms.header).select(
db.header.name, db.alarms.komment, db.headers.id, db.alarms.header
limitby=(0, 5)
)
Since original version works in MySQL and SQLite, I think to make DAL
more DB-independent, this feature should be added to the _select
method. I could not (due to time restrictions) fix it myself in DAL,
but hopefully Massimo as author will make it easily. The resulting
query should include the fields in question in the most inner nested
request, i.e.:
return '%s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s
<<<conditions fields go here>>> FROM %s%s%s) w_tmp WHERE ROWNUM<=%i)
%s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t,
sql_w_row, sql_o)
leaving outer select with only the fields requested by SQLSet.
Hope this helps,
Sergey
On Mar 1, 5:22 pm, SergeyPo <ser...@zarealye.com> wrote:
> Limitby clause works when you select db.table.ALL
>
> It must be possible to use aliases carefully to be able to select
> eequired fields only.
>
> On 28 фев, 16:02, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
>
>
> > No limitby is not working on Oracle with Joins. This is because Oracle
> > does not support pagination. We do it anyway using a trick (three
> > nested select as suggested by the oracle docs) but the trick breaks
> > down with joins.
>
> > On Feb 27, 11:50 am, SergeyPo <ser...@zarealye.com> wrote:
>
> > > If you remove limitby it works. I reported this error about 6 months
> > > ago, tried many workarounds and thought we fixed it. But somehow it
> > > appeared at my client's site. Versions 1.65 and current 1.75
> > > downloaded this week.
>
> > > On Feb 27, 8:20 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
>
> > > > Just going out on a limb here.
>
> > > > Have you tried the query where it selects all columns instead of just
> > > > name and komment?
>
> > > > -Thadeus
>
> > > > 2010/2/27 SergeyPo <ser...@zarealye.com>:
>
> > > > > SELECT headers1.name, alarms1.komment FROM (SELECT w_tmp.*, ROWNUM
> > > > > w_row FROM (SELECT headers1.name, alarms1.komment FROM headers1,
> > > > > alarms1 WHERE headers1.id=alarms1.header ORDER BY headers1.id,
> > > > > alarms1.id) w_tmp WHERE ROWNUM<=5) headers1, alarms1 WHERE
> > > > > headers1.id=alarms1.header AND w_row > 0;
>
> > > > > Traceback (most recent call last):
> > > > > File "c:\web2pyNEW\gluon\restricted.py", line 173, in restricted
> > > > > exec ccode in environment
> > > > > File "c:/web2pyNEW/applications/test/controllers/default.py", line 11,
> > > > > in <module>
> > > > > File "c:\web2pyNEW\gluon\globals.py", line 96, in <lambda>
> > > > > self._caller = lambda f: f()
> > > > > File "c:/web2pyNEW/applications/test/controllers/default.py", line 7,
> > > > > in index
> > > > > data = db(db.headers1.id==db.alarms1.header).select(db.headers1.name,
> > > > > db.alarms1.komment, limitby=(0, 5))
> > > > > File "c:\web2pyNEW\gluon\sql.py", line 3042, in select
> > > > > rows = response(query)
> > > > > File "c:\web2pyNEW\gluon\sql.py", line 3037, in response
> > > > > db._execute(query)
> > > > > File "c:\web2pyNEW\gluon\sql.py", line 970, in <lambda>
> > > > > oracle_fix_execute(a,self._cursor.execute)
> > > > > File "c:\web2pyNEW\gluon\sql.py", line 603, in oracle_fix_execute
> > > > > return execute(command[:-1], args)
> > > > > DatabaseError: ORA-00904: "HEADERS1"."ID": invalid identifier
>
> > > > > On 26 фев, 21:22, Thadeus Burgess <thade...@thadeusb.com> wrote:
> > > > >> please print the output of
>
> > > > >> db(db.headers.id == db.alarms.header)._select(db.header.name,
> > > > >> db.alarms.komment, limitby=(0,5))
>
> > > > >> -Thadeus
>
> > > > >> On Fri, Feb 26, 2010 at 12:00 PM, SergeyPo <ser...@zarealye.com>
> > > > >> wrote:
> > > > >> > Limitby clause still is not working with Oracle backend when used
> > > > >> > in
> > > > >> > queries that have any kind of join, left or inner.
>
> > > > >> > db.define_table('headers',
> > > > >> > SQLField('name', 'string')
> > > > >> > )
>
> > > > >> > db.define_table('alarms',
> > > > >> > SQLField('header', db.headers),
> > > > >> > SQLField('komment', 'string')
> > > > >> > )
>
> > > > >> > data = db(db.headers.id == db.alarms.header).select(
> > > > >> > db.header.name, db.alarms.komment, limitby=(0, 5)
> > > > >> > )
>
> > > > >> > ...gives Oracle error "wrong identifier 'alarms'.'headers'
>
> > > > >> > But in fact this is not matter of wrong fields, problem is with
> > > > >> > limitby construction.
>
> > > > >> > Queries using left join also give error, but another one. In both
> > > > >> > cases error message is quite irrelevant to real problem. E.g. I
> > > > >> > need a
> > > > >> > pretty complex query with groupby, count and left join... Error
> > > > >> > message in this case will be 'this is not valid groupby clause'.
> > > > >> > Obviously, problem is in aliases that are used for nested queries
> > > > >> > required by Oracle to limit rows in result set.
>
> > > > >> > Any help with DAL or workarounds please! I am ready to help with
> > > > >> > testing.
>
> > > > >> > --
> > > > >> > You received this message because you are subscribed to the Google
> > > > >> > Groups "web2py-users" group.
> > > > >> > To post to this group, send email to web...@googlegroups.com.
> > > > >> > To unsubscribe from this group, send email to
> > > > >> > web2py+unsubscr...@googlegroups.com.
> > > > >> > For more options, visit this group
> > > > >> > athttp://groups.google.com/group/web2py?hl=en.-Скрыть цитируемый
> > > > >> > текст -
>
> > > > >> - Показать цитируемый текст -
>
> > > > > --
> > > > > You received this message because you are subscribed to the Google
> > > > > Groups "web2py-users" group.
> > > > > To post to this group, send email to web...@googlegroups.com.
> > > > > To unsubscribe from this group, send email to
> > > > > web2py+unsubscr...@googlegroups.com.
> > > > > For more options, visit this group
> > > > > athttp://groups.google.com/group/web2py?hl=en.
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
