Urgent , How to write this SQL

This query appears to either require a position and/or an order to meet your requirements. I suggest looking at adding a new column to track order or use the pseudo-column rownum to handle location.

select 'a', 'b', 'c1', 'd1'
from (select 'a', 'b', 'c1', rownum from t1) a, (select d1, rownum from t2) b
where a.rownum = b.rownum(+) OR a.rownum = b.rownum-1 OR a.rownum = b.rownum+1


I hope this helps.

[Edited by Zaggy on 06-01-2001 at 12:39 AM]

Hi , Zaggy :
The solution is good . In fact , I have created the view to
hand the rownum column . But the problem is , The combination of A and B are multiple in T1 (T2) , So rownum
will show the sequence no of this record in whole table . So still can not achivement the desired result .

Any other solution ?

Any further instruction ?

This is the query you want, I believe. If it were possible to have more records in T2 than T1 at any time, you would need to do a UNION of the same statement with the outer-joins flipped - make sense? This statement will only work for T1 set > T2 set.

SELECT
---T1.Col1,
---T1.Col2,
---T1.Col3,
---T2.Col3
------AS Col4
FROM
---(
------SELECT
---------T1.*,
---------ROW_NUMBER()---
------------OVER---(
---------------PARTITION BY
------------------Col1,
------------------Col2
---------------ORDER BY
------------------Col3
---------------)
------------AS RN
------FROM
---------T1
---)---T1,
---(
------SELECT
---------T2.*,
---------ROW_NUMBER()---
------------OVER---(
---------------PARTITION BY
------------------Col1,
------------------Col2
---------------ORDER BY
------------------Col3
---------------)
------------AS RN
------FROM
---------T2
---)---T2
WHERE
---T2.Col1---(+)=---T1.Col1---AND
---T2.Col2---(+)=---T1.Col2---AND
---T2.RN------(+)=---T1.RN

(as always - replace --- with a 3-char tab for things to line up)

Now, the only trick with Analytical functions is that PL/SQL can't handle them. If this statement is to be run from a PL/SQL block, then the statement will need to be changed into a dynamic SQL statement - make sense? It's not my fault Oracle is stupid - they are fixing this oversight in 9i ;)

Let me know if you this doesn't work

- Chris

Hi , Friend ,
Your query is absolute right ( I have not run it , but from the
logic , I feel it's correct ) . The difficulty is make the PL/SQL function . I will study on it .
Anyway , thanks a lot for your help .
best regards
ligang