Prev: Trigger Invalid State
Next: My Oracle DBA Blog.
From: General Fear on 20 Jun 2008 00:32

I am using 10g.

Below is a crude example of what I am trying to do

Procedure
( pi_input as interger
)

is

Cursor A
is
Select 'A' as Letter from Dual;

Cursor B
is
Select 'A' as Letter from Dual;
Union
Select 'B' as Letter from Dual;

If pi_input = 1 then
Open A
Else
Open B
End If

Notice that Select 'A' as Letter from Dual is in two cursors. If I
have to make a change to Cursor A, I also have to do it again in
cursor B because "Select 'A' as Letter from Dual' is in two cursors.

Is it possible to eliminate repeat code in the above example? What do
I need to do.
From: Carlos on 20 Jun 2008 02:58
On 20 jun, 06:32, General Fear <richma...(a)earthlink.net> wrote:
> I am using 10g.
>
> Below is a crude example of what I am trying to do
>
> Procedure
> ( pi_input as interger
> )
>
> is
>
> Cursor A
> is
> Select 'A' as Letter from Dual;
>
> Cursor B
> is
> Select 'A' as Letter from Dual;
> Union
> Select 'B' as Letter from Dual;
>
> If pi_input = 1 then
> Open A
> Else
> Open B
> End If
>
> Notice that Select 'A' as Letter from Dual is in two cursors. If I
> have to make a change to Cursor A, I also have to do it again in
> cursor B because "Select 'A' as Letter from Dual' is in two cursors.
>
> Is it possible to eliminate repeat code in the above example? What do
> I need to do.

Not so sure about what you're trying to do but:

Select 'A' as Letter from Dual
Union ALL
Select 'B' as Letter from Dual
WHERE 1 = :pi_input;

should eliminate redundant code.

HTH

Cheers.

Carlos.
From: Robert Klemme on 20 Jun 2008 04:31
On Jun 20, 6:32 am, General Fear <richma...(a)earthlink.net> wrote:
> I am using 10g.
>
> Below is a crude example of what I am trying to do
>
> Procedure
> ( pi_input as interger
> )
>
> is
>
> Cursor A
>    is
> Select 'A' as Letter from Dual;
>
> Cursor B
>   is
> Select 'A' as Letter from Dual;
>    Union
> Select 'B' as Letter from Dual;
>
> If pi_input = 1 then
>   Open A
> Else
>   Open B
> End If
>
> Notice that Select 'A' as Letter from Dual  is in two cursors. If I
> have to make a change to Cursor A, I also have to do it again in
> cursor B because "Select 'A' as Letter from Dual' is in two cursors.
>
> Is it possible to eliminate repeat code in the above example? What do
> I need to do.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1392

robert
From: General Fear on 20 Jun 2008 06:54
On Jun 20, 1:58 am, Carlos <miotromailcar...(a)netscape.net> wrote:
> On 20 jun, 06:32, General Fear <richma...(a)earthlink.net> wrote:
>
>
>
> > I am using 10g.
>
> > Below is a crude example of what I am trying to do
>
> > Procedure
> > ( pi_input as interger
> > )
>
> > is
>
> > Cursor A
> > is
> > Select 'A' as Letter from Dual;
>
> > Cursor B
> > is
> > Select 'A' as Letter from Dual;
> > Union
> > Select 'B' as Letter from Dual;
>
> > If pi_input = 1 then
> > Open A
> > Else
> > Open B
> > End If
>
> > Notice that Select 'A' as Letter from Dual is in two cursors. If I
> > have to make a change to Cursor A, I also have to do it again in
> > cursor B because "Select 'A' as Letter from Dual' is in two cursors.
>
> > Is it possible to eliminate repeat code in the above example? What do
> > I need to do.
>
> Not so sure about what you're trying to do but:
>
> Select 'A' as Letter from Dual
> Union ALL
> Select 'B' as Letter from Dual
> WHERE 1 = :pi_input;
>
> should eliminate redundant code.
>
> HTH
>
> Cheers.
>
> Carlos.

Thanks. This is a good lead.

The problem is the user will be given two choices. If they click one
way. It is a simple select in Oracle, however, if they pick another
option on the Visual Basic GUI, that means several unions.

I wanted to avoid maintaining several cursors that are basically the
same.

Thanks!
 | 
Pages: 1
Prev: Trigger Invalid State
Next: My Oracle DBA Blog.