Thank you. I will send another round of tests.
Massimo
On Dec 24, 10:16 am, Mike <michael.mcgreg...@gmail.com> wrote:
> Hi Massimo -
>
> They all threw errors. I listed the errors and how I fixed them below.
> All of this was done directly in SQL SERVER 2005, but no test data.
>
> Thanks for taking a look at this! I hope this info helps...
>
> --****************
> --STATEMENT 1
> --****************
>
> --ERROR
> --Msg 8120, Level 16, State 1, Line 1
> --Column 't1.id' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
> --FIXED
> SELECT t1.name
> FROM t1
> WHERE t1.id>0
> GROUP BY t1.name ORDER BY t1.name;
>
> --COMMENTS
> --Removed t1.id from the SELECT
>
> --****************
> --STATEMENT 2
> --****************
>
> --ERROR
> --Msg 156, Level 15, State 1, Line 2
> --Incorrect syntax near the keyword 'GROUP'.
>
> --FIXED
> SELECT t2_id, t2_t1, t2_name, t1_id, t1_name
> FROM (SELECT t2.id AS t2_id,
> t2.t1 AS t2_t1,
> t2.name AS t2_name,
> t1.id AS t1_id,
> t1.name AS t1_name,
> ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown
> FROM t2, t1
> WHERE t1.id=t2.t1) AS derived_table
> WHERE w_rown>=1 AND w_rown<2
> GROUP BY t2_id, t2_t1, t2_name, t1_id, t1_name
> ORDER BY t1_name;
>
> --COMMENTS
> --I don't think you want the GROUP BY in the OVER ()??.
> --I switched the WHERE clauses for the derived table and the outer
> query. That provides the join information to the inner query and the
> outer query selects the set of rows requested
> --Ambiguous column names come into play since the t1 and t2 table
> aliases are only available in the derived table. To work around that I
> had to give each column in the derived table a unique alias. But those
> would probably need to be converted back to table.column (vs.
> table_column) before returning the set to the user
> --Finally each non-aggregate column in the select list has to be in
> the group by.
>
> --****************
> --STATEMENT 3
> --****************
>
> --ERROR AND COMMENTS SAME AS STATEMENT 2
> --FIXED
> SELECT t1_id, t1_name, t2_id, t2_t1, t2_name
> FROM (SELECT t1.id AS t1_id,
> t1.name AS t1_name,
> t2.id AS t2_id,
> t2.t1 AS t2_t1,
> t2.name AS t2_name,
> ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown
> FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 ) AS derived_table
> WHERE w_rown>=1 AND w_rown<2
> GROUP BY t1_id, t1_name, t2_id, t2_t1, t2_name
> ORDER BY t1_name;
>
> On Dec 24, 4:53 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > 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 <mdipie...@cs.depaul.edu> 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 <michael.mcgreg...@gmail.com> 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.htmandrequiresSQL2005'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 web2py@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
-~----------~----~----~----~------~----~------~--~---
