Well, I'm not entirely convinced that it's an intractable problem...
In reality, although the syntax differs for each DB, there are only a
fixed number of syntaxes and Transfer *does* know which DB you're
using (mssql, mysql, postgresql, oracle) so there are only four
syntaxes max.
If someone can list the four syntaxes for those DB types, I'm willing
to have a go at implementing it inside the QueryBuilder and working
with Mark on integrating it.
I *think* it's
MS SQL:
SELECT TOP n rest_of_query
MySQL / PostgreSQL:
SELECT rest_of_query LIMIT n
and for Oracle:
SELECT columns_etc WHERE ROWNUM < n AND rest_of_where_clause
(Oracle being the trickiest of course!)
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood
-TM
-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
On Behalf Of Sean Corfield
Sent: Tuesday, September 11, 2007 8:52 PM
To: transf...@googlegroups.com
Subject: [transfer-dev] Re: Implementing TOP / LIMIT
If I remember correct you need to subselect the query, because if you
order by, the rownum is out of order with what is originally in the
query.
LIke so: http://www.anticlue.net/archives/000391.htm
I don't know if this was changed in 9i+ (since Transfer only supports 9i+)
Mark
Guillaume Apollinaire quotes
Atm, I'm knee deep in CF8 performance improvements (and some for CF7
too), while I'm also waiting for some feedback on the composite key
CRUD stuff (hint hint ;o) )
Mark
this is a sample query with cases for mssql, oracle, postgresql, mysql
and db2.
<cfquery name="qData"
datasource="#variables.stContens.sDatasource#">
SELECT
<!--- limit rows, rdbms specific --->
<cfif variables.stContens.sDbSys is
"mssql" AND arguments.iMaxrows GT 0>
TOP #arguments.iMaxrows#
</cfif>
co_apps.app_ID,co_apps.appname,...
FROM co_apps
WHERE
<!--- limit rows, rdbms specific --->
<cfif variables.stContens.sDbSys is
"oracle" AND arguments.iMaxrows GT 0>
AND ROWNUM <=
#arguments.iMaxrows#
</cfif>
<!--- sort order --->
<cfif len(arguments.sortOrder)>
ORDER BY #arguments.sortOrder#
</cfif>
<!--- limit rows, rdbms specific --->
<cfif arguments.iMaxrows GT 0>
<cfif ListFind("pgsql,mysql",
variables.stContens.sDbSys)>
LIMIT #arguments.iMaxrows#
<cfelseif variables.stContens.sDbSys IS
"db2">
FETCH FIRST #arguments.iMaxrows#
ROWS ONLY
</cfif>
</cfif>
</cfquery>
--
Harry Klein | Konzeption und Entwicklung
CONTENS Software GmbH
Oettingenstr. 25 | 80538 München
Fon: +49 (0)89 5199 69-0 | Fax: +49 (0)89 5199 69-78
mailto:kl...@contens.de | http://www.contens.de
****************************
Sitz der Gesellschaft: München
Geschäftsführer: Jens Hoppe
Amtsgericht München, HRB 128 273
> -----Ursprüngliche Nachricht-----
> Von: transf...@googlegroups.com
> [mailto:transf...@googlegroups.com] Im Auftrag von Sean Corfield
> Gesendet: Mittwoch, 12. September 2007 02:52
> An: transf...@googlegroups.com
> Betreff: [transfer-dev] Re: Implementing TOP / LIMIT
It seems that in Oracle ROWNUM is processed before an ORDER BY, which
doesn't yield what we'd expect if you have an ORDER BY (unless it's changed
in recent versions). Here's an article that illustrates that:
http://www.oracle-base.com/articles/misc/TopNQueries.php This article also
illustrates how to make things work properly in Oracle, which would be as
follows:
SELECT id
FROM (SELECT id
FROM rownum_order_test
ORDER BY id DESC)
WHERE rownum <= 5
-TM
-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
Thanks for this hint Thomas. I tested with Oracle 10, this behaviour was
not changed.
-Harry