Making some progress but I need your help.
Given this:
db.define_table('t1',SQLField('name'))
db.define_table('t2',SQLField('t1',db.t1),SQLField('name'))
Are these valid MSSQL Statements?
1) db(db.t1.id>0)._select(orderby=db.t1.name,groupby=db.t1.name)
'SELECT t1.id, t1.name FROM t1 WHERE t1.id>0 GROUP BY t1.name ORDER BY
t1.name;'
2) db(db.t1.id==db.t2.t1)._select
(orderby=db.t1.name,groupby=db.t1.name,limitby=(1,2))
'SELECT t2.id, t2.t1, t2.name, t1.id, t1.name FROM (SELECT t2.id,
t2.t1, t2.name, t1.id, t1.name, ROW_NUMBER() OVER( GROUP BY t1.name
ORDER BY t1.name) AS w_rown FROM t2, t1 WHERE w_rown>=1 AND w_rown<2)
WHERE t1.id=t2.t1 GROUP BY t1.name ORDER BY t1.name;'
3) db()._select(db.t1.ALL,db.t2.ALL,left=db.t2.on
(db.t1.id==db.t2.t1),orderby=db.t1.name,groupby=db.t1.name,limitby=
(1,2))
'SELECT t1.id, t1.name, t2.id, t2.t1, t2.name FROM (SELECT t1.id,
t1.name, t2.id, t2.t1, t2.name, ROW_NUMBER() OVER( GROUP BY t1.name
ORDER BY t1.name) AS w_rown FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 WHERE
w_rown>=1 AND w_rown<2) GROUP BY t1.name ORDER BY t1.name;'
If not, how should they be fixed (SQL)?
Massimo
On Dec 23, 4:59 pm, mdipierro <[email protected]> wrote:
> I think so. This is what we do with Oracle. Tomorrow.
> Thank you for the example. It helps.
>
> Massimo
>
> On Dec 23, 1:26 pm, Mike <[email protected]> wrote:
>
> > Hi Everyone,
>
> > As the books notes, limitby isn't fully supported by mssql since the
> > TOP command always starts at index 0.
>
> > So to implement paging I ended up writing my own SQL to mimic the
> > limitby behavior. The main example I used was
> > fromhttp://www.15seconds.com/issue/070628.htmandrequires SQL 2005's
> > ROW_NUMBER function.
>
> > My web2py controller snippet:
>
> > #build the SQL query
> > query = "SELECT " + select_cols + " FROM (SELECT ROW_NUMBER() OVER
> > (ORDER BY "+sidx+" "+sord+") AS rownum, " + select_cols + " FROM "+
> > from_tbl + where_clause
> > #close the inner query and finish the outer query by using the
> > rownum() to return only the rows requested by the
> > user
> > query = query + ") AS tbl1 WHERE rownum >= "+str(first_rownum)+"
> > AND rownum < "+str(last_rownum)+" ORDER BY " + sidx + " " + sord
>
> > This produces something to the effect of...
> > SELECT person, income
> > FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
> > rownum, person, income FROM Salaries) AS Salaries1
> > WHERE rownum >= 5 AND rownum <= 9
> > ORDER BY income
>
> > Note that the row numbers start with 1 (not 0) by default.
>
> > My question is could this method be incorporated into the DAL? I've
> > peeked at the sourcecode by I must admit it's a bit over my head
> > still. If it is possible I'm willing to do my best to help with a
> > patch but would need a fair amount of help.
>
> > Thanks!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
