Re: using a variable as a "macro"

"cumin" <jkilbourne_at_gmail.com> wrote:

>Is there a way to access an environmental variable in sqlplus? A lot of
>the ad hoc queries I run have the phrase
>
>and tty in ('IN','BN','SCD','SBD','SCDF','SBDF','SCDC','SBDC')
>
>and I am too lazy to type that in so much. Is there a way to set and
>retrieve a string variable in sqlplus that will let mr put this string
>into numerous queries? I'd like the vairable to live in the databse,
>but if it needs to be an OS environent variable that's ok too.
>
>Thanks

Build a lookup table containing those values and use it in the where clause: where tty in ( select tty from new_lookup_table)

You could also add a category field so that you can store several lookup values for different needs..

New_lookup_table:
Category varchar2(30)
Vals varchar2(200)

insert into new_lookup_table values ("tty","IN")....etc(repeat until all populated)

then you can use:

where tty in ( select Vals from new_lookup_table where Category = 'tty');

hth Received on Fri Dec 16 2005 - 16:56:18 CET