Home » SQL & PL/SQL » SQL & PL/SQL » select statement help!
select statement help! [message #193078] Thu, 14 September 2006 15:59 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
I might be too ambiguous... but this is what I am trying to do with the select statement...

select 
  tab1.col1,
  decode(tab2.col2, <if exists>, tab2.col2, 0)
from tab1, tab2
where <tab1 and tab2 join condition>;


I am not sure how I can do that with decode function, or is there any better way to do that.

Thanks,
Ozzy
Re: select statement help! [message #193082 is a reply to message #193078] Thu, 14 September 2006 17:44 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SQL> SELECT t1.*
  2  FROM t1
  3  ORDER BY t1.a;

         A          B
---------- ----------
         1         10
         2         11
         3         12
         4         13
         5         14
         6         15
         7         16

7 rows selected

SQL> SELECT t2.*
  2  FROM t2
  3  ORDER BY t2.a;

         A          B
---------- ----------
         1         20
         3 
         4         30
         5 
         7         40

SQL> SELECT t1.*
  2        ,t2.*
  3        ,nvl(t2.b, 0)
  4  FROM t1
  5      ,t2
  6  WHERE t2.a(+) = t1.a
  7  ORDER BY t1.a;

         A          B          A          B NVL(T2.B,0)
---------- ---------- ---------- ---------- -----------
         1         10          1         20          20
         2         11                                 0
         3         12          3                      0
         4         13          4         30          30
         5         14          5                      0
         6         15                                 0
         7         16          7         40          40

7 rows selected
Re: select statement help! [message #193312 is a reply to message #193082] Fri, 15 September 2006 11:19 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Thanks Scott...

This is what I need...

test_tab1, test_tab2, and test_tab3 are the input table and 'Desired Result' is what I want. I cannot do outter joins on two different table....

test_tab1
A
=
1
2
3

test_tab2
B
=
a
b


test_tab3
A B C  
= = ===
1 a 100
2 b 200
3 a 500

Desired Result
A B C  
= = ===
1 a 100
1 b 0
2 a 0
2 b 200
3 a 500
3 b 0


Please advice....

[Updated on: Fri, 15 September 2006 11:35]

Report message to a moderator

Re: select statement help! [message #193315 is a reply to message #193312] Fri, 15 September 2006 11:52 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SQL> SELECT * FROM test_tab1;

         A
----------
         1
         2
         3

SQL> SELECT * FROM test_tab2;

B
-
a
b

SQL> SELECT * FROM test_tab3;

         A B          C
---------- - ----------
         1 a        100
         2 b        200
         3 a        500

SQL> SELECT t1_2.a
  2        ,t1_2.b
  3        ,nvl(t3.c, 0)
  4  FROM (SELECT *
  5        FROM test_tab1
  6            ,test_tab2) t1_2
  7       ,test_tab3 t3
  8  WHERE t3.a(+) = t1_2.a
  9    AND t3.b(+) = t1_2.b
 10  ORDER BY 1,2;

         A B NVL(T3.C,0)
---------- - -----------
         1 a         100
         1 b           0
         2 a           0
         2 b         200
         3 a         500
         3 b           0

6 rows selected
Previous Topic: creating a temp table...pls help
Next Topic: Relation between sequence and column (not the autoincrement problem)
Goto Forum:
  


Current Time: Sun Dec 04 00:44:20 CST 2016

Total time taken to generate the page: 0.26379 seconds