 | |
SELECT Question
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#61
 
| |||
| |||
|
Re: SELECT Question -
06-18-2008
, 03:15 AM
|
Quick question here, if I have a WHERE clause with an IN condition - WHERE var IN (1, 2, 3)...... What if the list is dynamic? How would you do that? |
SQL> create type ListType as table of number ;
2 /
SQL> declare
2 ids ListType := ListType() ;
3 names owa_text.vc_arr ;
4 begin
5 ids.extend(3) ;
6 ids(1) := 102 ;
7 ids(2) := 104 ;
8 ids(3) := 110 ;
9 select
10 LAST_NAME
11 bulk collect into
12 names
13 from
14 table(cast(ids as ListType)) ids,
15 employees emp
16 where
17 emp.employee_id = ids.column_value
18 ;
19* end ;
20 /
PL/SQL procedure successfully completed.
Y.
#62
| |||
| |||
|
|
Quick question here, if I have a WHERE clause with an IN condition - WHERE var IN (1, 2, 3)...... What if the list is dynamic? How would you do that? |
SQL> create type ListType as table of number ;
2 /
SQL> declare
2 ids ListType := ListType() ;
3 names owa_text.vc_arr ;
4 begin
5 ids.extend(3) ;
6 ids(1) := 102 ;
7 ids(2) := 104 ;
8 ids(3) := 110 ;
9 select
10 LAST_NAME
11 bulk collect into
12 names
13 from
14 table(cast(ids as ListType)) ids,
15 employees emp
16 where
17 emp.employee_id = ids.column_value
18 ;
19* end ;
20 /
PL/SQL procedure successfully completed.
Y.
#63
| |||
| |||
|
|
Quick question here, if I have a WHERE clause with an IN condition - WHERE var IN (1, 2, 3)...... What if the list is dynamic? How would you do that? |
SQL> create type ListType as table of number ;
2 /
SQL> declare
2 ids ListType := ListType() ;
3 names owa_text.vc_arr ;
4 begin
5 ids.extend(3) ;
6 ids(1) := 102 ;
7 ids(2) := 104 ;
8 ids(3) := 110 ;
9 select
10 LAST_NAME
11 bulk collect into
12 names
13 from
14 table(cast(ids as ListType)) ids,
15 employees emp
16 where
17 emp.employee_id = ids.column_value
18 ;
19* end ;
20 /
PL/SQL procedure successfully completed.
Y.
#64
| |||
| |||
|
|
Quick question here, if I have a WHERE clause with an IN condition - WHERE var IN (1, 2, 3)...... What if the list is dynamic? How would you do that? |
SQL> create type ListType as table of number ;
2 /
SQL> declare
2 ids ListType := ListType() ;
3 names owa_text.vc_arr ;
4 begin
5 ids.extend(3) ;
6 ids(1) := 102 ;
7 ids(2) := 104 ;
8 ids(3) := 110 ;
9 select
10 LAST_NAME
11 bulk collect into
12 names
13 from
14 table(cast(ids as ListType)) ids,
15 employees emp
16 where
17 emp.employee_id = ids.column_value
18 ;
19* end ;
20 /
PL/SQL procedure successfully completed.
Y.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.