On 12/5/06 11:17 PM, Clayton Scott wrote:
> Do you have some code I can run on an Oracle 9 database to test this?
The table definitions and the query are below.
> What if the table aliases are ta, tb etc instead of t1, t2,...?
No change.
-John
---
CREATE TABLE rose_db_object_other
(
k1 INT NOT NULL,
k2 INT NOT NULL,
k3 INT NOT NULL,
name VARCHAR(32),
PRIMARY KEY (k1, k2, k3)
);
CREATE TABLE rose_db_object_bb
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32)
);
CREATE TABLE rose_db_object_test
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
flag NUMBER(1) NOT NULL,
flag2 NUMBER(1),
status VARCHAR(32) DEFAULT 'active',
bits VARCHAR(5) DEFAULT '00101' NOT NULL,
fixed CHAR(16) DEFAULT 'needed',
start_date DATE,
save INT,
fk1 INT,
fk2 INT,
fk3 INT,
b1 INT REFERENCES rose_db_object_bb (id),
b2 INT REFERENCES rose_db_object_bb (id),
last_modified TIMESTAMP,
date_created TIMESTAMP,
FOREIGN KEY (fk1, fk2, fk3) REFERENCES rose_db_object_other (k1, k2, k3)
);
SELECT * FROM
(
SELECT a.*, ROWNUM oracle_rownum FROM
(
SELECT
t1.b1,
t1.b2,
t1.bits,
t1.date_created,
t1.fixed,
t1.fk1,
t1.fk2,
t1.fk3,
t1.flag,
t1.flag2,
t1.id,
t1.last_modified,
t1.name,
t1.save,
t2.k1,
t2.k2,
t2.k3,
t2.name
FROM
scott.rose_db_object_test t1
JOIN scott.rose_db_object_other t2 ON
(t1.fk2 = t2.k2 AND t1.fk1 = t2.k1 AND t1.fk3 = t2.k3)
ORDER BY t1.id DESC
) a
WHERE ROWNUM <= 10
)
WHERE oracle_rownum >= 9;
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
