Guys this is urgent

[paddy04]

I HAVE A PROBLEM IN HAND,

I have a table A with records as under

Col1 col2 col3
----- ----- -----
A 1 ok
B 1 ok
C 1 ok
D 1 ok

table B

Col1 col2 col3
----- ----- -----
C 0 ok
D 0 ok
E 0 ok
F 0 ok

I want to output to be

Col1 col2 col3
----- ----- -----
A 1 ok
B 1 ok
C 1 ok
D 1 ok
E 0 ok
F 0 ok


Thanks and regards,

Paddy

[aarroyob]

The only really urgent thing here is you need to read sql docs

[eeswar]

Paddy,

Please...What you have posted in not a problem.

Hope this helps.

select * from a
union
select * from b
where b.col1 not in ( select COL1 from a intersect select COL1 from b);

This will work considering the data pattern being the same as you have given in the example

[jrpm]

alternative

select a,max(b),c
from { select * from a
union
select * from b)
group by b
order by a,b,c

[Calvin_Qiu]

A BIT MODIFICATION

select a,max(b),c
from ( select * from a
union
select * from b)
group by a,c
order by a

aonther query:

SELECT DECODE(A.COL1, '', B.COL1, A.COL1) "C",
DECODE(A.COL2, '', 0, A.COL2)+DECODE(B.COL2, '', 0, B.COL2) "I",
DECODE(A.COL3, '', B.COL3, A.COL3) "OK"
FROM A FULL OUTER JOIN B ON (A.COL1=B.COL1)
ORDER BY C

[paddy04]

Calvin_Qiu,Joseph and eeswar thanks for the response..

Actually I was working on a querry which have lots of subquerries and unions..The problem I stated was just a part of that...

I have tried all the options submitted by you guys..

In my case Josephs soln seem to give a better performance..

And calvin the second query submitted by you seems to not work in Oracle...however I am very curious to work on it..

Thanks for all the help extended,

Regards,

Paddy

[Calvin_Qiu]

It is a new feature of Oracle 9i.

[paddy04]

Hi Calvin,

I am working on oracle 8i,
Can we rephrase this so that it will work in 8i as well..

I was interested in it because it doesnot have any union or a sub query..This will positively reduce the execution time..

If you look into this it will be of a great help.

Thanks for you help Calvin.


Warm Regards,

Paddy