PL/SQL (MOSC)

MOSC Banner

Refine Qurey

edited Feb 11, 2010 10:02AM in PL/SQL (MOSC) 14 commentsAnswered ✓
Hi, I am seeking a better way to refine the following conditional query.

DROP TABLE t1;
CREATE TABLE t1 (a NUMBER);
DROP TABLE t2;
CREATE TABLE t2 (b NUMBER);
DROP TABLE t3;
CREATE TABLE t3 (c NUMBER);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (3);
COMMIT;
/* Conditional
if match found in t2 then
  use t2
else
  find match in t3
end if; 
*/
SELECT t1.a FROM t1,t2 WHERE t1.a = t2.b
UNION
SELECT t1.a FROM t1,t3 WHERE t1.a = t3.c AND t1.a NOT IN (SELECT b FROM t2)

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center