Archives of the TeradataForum

Message Posted: Fri, 19 Jul 2002 @ 18:41:13 GMT

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

Hi Claybourne:

There is a way to do something like this, but you might have to think outside the box. However, there is an inherit danger with numeric data, discussed below.

Starting with this:

Create Macro Test(X varchar(32000))
/* I exaggerate the length here, make it what you need it to be */
As (Select * From TableA Where index(:X,CAST(ColumnA AS CHAR(11))) > 0;
);

Then, when you call the macro:

Exec Test ('1 2'); /* the spaces are important */

Here is the danger with using numeric data of mixed length instead of fixed length character data:

exec test ('10 1123');

When columnA = 10 it is selected. 10 char(2)= 10 char(2) - perfect. However, when columnA contains 11 or 12 or 23 char(2) = 11, 12, 23 (from 1123, 2 characters at a time)char(2). Therefore, it is imparative that the string lengths be identical.

As a safety value, you might add a CASE test on the length of columnA or do something like this:

Create Macro Test(X varchar(32000), length_value)
/* I exaggerate the length here, make it what you need it to be */
As (Select * From TableA Where index(:X,CAST(ColumnA AS CHAR(11))) > 0
and CHAR(CAST(ColumnA AS CHAR(11))) = :length_value;);

Exec test ('1 3 5 7 9',1);
Exec test ('10 11 13 58 77 91',2);

I am not at a system where I can verify that this works perfectly as written. At a minimum, you will probably need to adjust the data types or parameter values to match (types and lengths). But, I hope it gives you a more flexible approach as a starting point.

Regards,

Mike

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