Hello all,
I have been using nhibernate for sometime, and i am stable using 1.2.0.CR1 on a web app... I wanted to check out 1.2.1.GA but there is some problem when applying the methods SetFirstResult/SetMaxResult on an ISQLQuery...
The specific examples are with SetFirstResult(0) and SetMaxResult (20)
Hibernate version: 1.2.0.CR1 / 1.2.1.GA
The original sql query (ran fine with earlier version):
SELECT tic.ticketid, tic.createdate, tic.assigndate, tic.enddate, cust.firstname, cust.phone1 as "custphone", br.brandname, it.description, datediff(dd, tic.createdate, tic.enddate) as "days", sup.supname, sup.phone1 as "supphone" FROM ticket tic, customer cust, contract con, brand br, item it, supplier sup WHERE tic.fk_contractid = con.id AND con.fk_customerid = cust.customerid AND con.fk_itemid = it.itemid AND it.fk_brandid = br.brandid AND tic.fk_supplierid = sup.supid
The generated SQL (show_sql=true):
SELECT TOP 20 ticketid, createdate, assigndate, enddate, firstname, "custphone", brandname, description, datediff(dd, createdate, "days", supname, "supphone" FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__) as row, query.ticketid, query.createdate, query.assigndate, query.enddate, query.firstname, query."custphone", query.brandname, query.description, query. datediff(dd, query.createdate, query."days", query.supname, query."supphone", query.__hibernate_sort_expr_1__ FROM (SELECT tic.ticketid, tic.createdate, tic.assigndate, tic.enddate, cust.firstname, cust.phone1 as "custphone", br.brandname, it.description, datediff(dd, tic.createdate, tic.enddate) as "days", sup.supname, sup.phone1 as "supphone", FROM ticket tic, customer cust, contract con, brand br, item it, supplier sup WHERE tic.fk_contractid = con.id AND con.fk_customerid = cust.customerid AND con.fk_itemid = it.itemid AND it.fk_brandid = br.brandid AND tic.fk_supplierid = sup.supid ) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_1__
Note the Datediff function on the Select list of the generated Sql command. Running some other queries there seems to be some problem when functions and CASEs are included in the Select statement
|