-
Guys this is urgent
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
-
The only really urgent thing here is you need to read sql docs
-
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
Never Ever Give Up!
-
alternative
select a,max(b),c
from { select * from a
union
select * from b)
group by b
order by a,b,c
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
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
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
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
-
It is a new feature of Oracle 9i.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|