Archives of the TeradataForum

Message Posted: Mon, 28 Aug 2000 @ 10:06:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am looking for a way to specify multiple integer options in one macro parameter.

e.g. on the following table I need to select multiple regions and multiple store_no's

Stores table:
Region char(1)
Region_name varchar(20)
Store_no smallint
Store_name varchar(30)

Index is region,store_no

Create macro read_stores as (:reg varchar(30) )
as
(Select * from stores
Where index(:reg, region) > 0
Order by region, store_no;);

Works for the region code, which is character - exec read_stores('A B C'); How can I achieve the same result on the store_no field? Store numbers range from 1 to 30.

The following works, but there must be a better/more efficient solution:

sel *
from stores
where
index('*08*09*10*11*12*',('*'||substring(cast(store_no + 100 as char(3))
from 2 for 2))) > 0;

To allow for the 'all regions' options, I have set the macro as follows:

Create macro read_stores as (:reg varchar(30), rlim byteint )
as
(Select * from stores
Where index(:reg, region) > :rlim
Order by region, store_no;);

Where rlim is 0, the region must exist in the list Where rlim is -1, any region code will match, so all regions are selected. Is there another way of achieving the same result without having a separate macro for each case?

The final macro will run against large tables, with a number of these multi-selection parameters.

Any examples, advice or comments are welcome.

Thanks

Barbara George
Manager, Corporate Reporting
Pick 'n Pay, Cape Town, South Africa

You can't get ahead if you're trying to get even

	<Prev	Next>		<<First	<Prev	Next>	Last>>