Home » SQL & PL/SQL » SQL & PL/SQL » PL-SQL set object or selection from table variable
PL-SQL set object or selection from table variable [message #37418] Wed, 06 February 2002 21:37 Go to next message
Denis
Messages: 8
Registered: December 2000
Junior Member
Hi group,
I need some help with the in-clause for a select statement. Basically I have a prcedure with one parameter:
procedure foo(a integer)
if a's value is say zero I want to perform a selection from a table into a sequence (or table or varray I don't know).

if a = 0 then
<set variable> := select id from table tA;
else
<set variable> := select id from table tA where id = a;

I think this is possible:
select b from tB where id in (select id from table tA);
or this
select b from tB where id in (1, 2, 3);

the problem is that I need to define this set prior to selection.

Is there a way to do this or I need to use dynamic sql?

Thans,
Denis
Re: PL-SQL set object or selection from table variable [message #37424 is a reply to message #37418] Thu, 07 February 2002 01:03 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

try this statement instead of two

select id from table tA
where id = decode(a,0,id,a);

and

select b from tB where id in
(select id from table tA
where id = decode(a,0,id,a)
)

no need to use dynamic pl/sql
cheers
pratap
Previous Topic: create package
Next Topic: INSERT using BIG subquery
Goto Forum:
  


Current Time: Thu Mar 28 07:37:17 CDT 2024