>> I need Oracle ROWNUM equivalent. For doing following operation ----------- ------------ ------------- VIEW
Y.XL VIEW Y.XR VIEW Y_XL_XR 1 + 5 = 1 5 6 10
6 10 12 20 12 20With Oracle I can use ROWNUM as key to join the two views; Is there
any other ways to do that? <<
Never use a row number. It is not part of a logical model, it is not
relational, it is not portable. Please post DDL, so that people do
not have to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema. Here is what I am
assuming:
I have two tables. Rows in Table A have a column x which needs to be
set to the primary key of a row from Table B. The first row of table
A should point to the first row of table B, the second row of Table A
should point to the second row of table B, etc. There are many more
rows in Table A than in Table B and I would like to match column x to
Table B in a round-robin fashion.
CREATE TABLE A
(keycol INTEGER NOT NULL PRIMARY KEY, x INTEGER)
INSERT INTO A VALUES (1, NULL);
INSERT INTO A VALUES (2, NULL);
INSERT INTO A VALUES (3, NULL);
INSERT INTO A VALUES (7, NULL);
INSERT INTO A VALUES (10, NULL);
CREATE TABLE B
(keycol INTEGER NOT NULL PRIMARY KEY);
INSERT INTO B VALUES (10);
INSERT INTO B VALUES (24);
INSERT INTO B VALUES (32);
Warning: while this is portable, it is not going to be fast.
UPDATE A SET x = (SELECT keycol FROM B WHERE ((SELECT COUNT(A1.keycol) FROM
AAS A1 WHERE A1.keycol < A.keycol) % (SELECT COUNT(*) FROM B)) +1
= (SELECT COUNT(B1.keycol) FROM B AS B1 WHERE B1.keycol <= B.keycol));
SELECT * FROM A;
(5 row(s) affected)
keycol x
----------- -----------
1 10
2 24
3 32
7 10
10 24
Basically, I use the keys to determining the size of the subset of key
columns within a boundary established by the current row in each table
via those correlated subquery expressions.
The rest is MOD arithmetic; add one to get rid of the 0 in the modulus
cycle, use < and <= in the comparisons to adjust the subset counts.
--CELKO--