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.htmandrequiresSQL 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 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
-~----------~----~----~----~------~----~------~--~---
