Not at work now so can't check the syntax but with a as ( select t1.col1 from t1 where col1 is not null union all select t2.col1 from t2 where col1 is not null order by 1) b as ( select t1.col2 from t1 where col2 is not null union all select t2.col2 from t2 where col2 is not null order by 1) select col1,col2 from a left outer join b on 1 = 1;
Mike On Sat, Oct 22, 2011 at 2:50 AM, Binu K S <[email protected]> wrote: > SELECT > X.COL1 , Y.COL2 > > FROM > > ( > > SELECT > ROWNUM XROW, COL1 AS COL1 FROM > > ( > > SELECT > COL1 FROM > > ( > > SELECT > COL1 FROM T1 WHERE COL1 IS NOT NULL > > UNION > ALL > > SELECT > COL1 FROM T2 WHERE COL1 IS NOT NULL > > ) > > ORDER > BY 1 > > ) > > ) > X > > FULL > OUTER JOIN > > ( > > SELECT > ROWNUM YROW , COL2 AS COL2 FROM > > ( > > SELECT > COL2 FROM > > ( > > SELECT > COL2 FROM T1 WHERE COL2 IS NOT NULL > > UNION > ALL > > SELECT > COL2 FROM T2 WHERE COL2 IS NOT NULL > > ) > > ORDER > BY 1 > > ) > > ) > > Y > > ON ( XROW = YROW) > > On Sat, Oct 22, 2011 at 1:01 PM, SANDEEP REDDY > <[email protected]>wrote: > >> Here Are the inputs of corresponding tables >> >> >> >> SQL> select * from t1; >> >> COL1 COL2 >> ---------- ---------- >> 1 - >> 2 - >> - 55 >> 3 11 >> 4 - >> 5 66 >> >> 6 rows selected. >> >> SQL> select * from t2; >> >> COL1 COL2 >> ---------- ---------- >> 8 - >> - 33 >> - - >> - 88 >> 10 - >> >> >> I Need A output like >> >> COL1 COL2 >> 1 11 >> 2 33 >> 3 55 >> 4 66 >> 5 88 >> 8 >> 10 >> >> Source Code : >> >> CREATE TABLE T1 >> ( >> COL1 NUMBER, >> COL2 NUMBER >> ); >> >> CREATE TABLE T2 >> ( >> COL1 NUMBER, >> COL2 NUMBER >> ); >> >> >> Insert into T1 >> (COL1, COL2) >> Values >> (1, NULL); >> Insert into T1 >> (COL1, COL2) >> Values >> (2, NULL); >> Insert into T1 >> (COL1, COL2) >> Values >> (NULL, 55); >> Insert into T1 >> (COL1, COL2) >> Values >> (3, 11); >> Insert into T1 >> (COL1, COL2) >> Values >> (4, NULL); >> Insert into T1 >> (COL1, COL2) >> Values >> (5, 66); >> >> >> >> >> Insert into T2 >> (COL1, COL2) >> Values >> (8, NULL); >> Insert into T2 >> (COL1, COL2) >> Values >> (NULL, 33); >> Insert into T2 >> (COL1, COL2) >> Values >> (NULL, NULL); >> Insert into T2 >> (COL1, COL2) >> Values >> (NULL, 88); >> Insert into T2 >> (COL1, COL2) >> Values >> (10, NULL); >> COMMIT; >> >> >> TRY IT FRIENDZ.... >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
