SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 13 11:34:14 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE stores (
  2  store_name      VARCHAR2(20),
  3  region_dir      NUMBER(5),
  4  region_mgr      NUMBER(5),
  5  store_mgr1      NUMBER(5),
  6  store_mgr2      NUMBER(5),
  7  asst_storemgr1  NUMBER(5),
  8  asst_storemgr2  NUMBER(5),
  9  asst_storemgr3  NUMBER(5));

Table created.

SQL>
SQL> INSERT INTO stores
  2  VALUES ('San Francisco',100,200,301,302,401,0,403);

1 row created.

SQL>
SQL> INSERT INTO stores
  2  VALUES ('Oakland',100,200,301,0,404,0,0);

1 row created.

SQL>
SQL> INSERT INTO stores
  2  VALUES ('Palo Alto',100,200,0,305,0,405,406);

1 row created.

SQL>
SQL> INSERT INTO stores
  2  VALUES ('Santa Clara',100,250,0,306,0,0,407);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT DECODE(asst_storemgr1, 0,
  2         DECODE(asst_storemgr2, 0,
  3         DECODE(asst_storemgr3, 0, 0, asst_storemgr3),
  4         asst_storemgr2), asst_storemgr1) ASST_MANAGER,
  5         DECODE(store_mgr1,0, DECODE(store_mgr2,0, 0, store_mgr2),
  6         store_mgr1) STORE_MANAGER,
  7         REGION_MGR,
  8         REGION_DIR
  9  FROM stores;

ASST_MANAGER STORE_MANAGER REGION_MGR REGION_DIR
------------ ------------- ---------- ----------
         401           301        200        100
         404           301        200        100
         405           305        200        100
         407           306        250        100

SQL>

Download Code