Archives of the TeradataForum

Message Posted: Sat, 09 Jul 2005 @ 05:27:21 GMT

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

The commas will cause you a problem. So, instead of using some number of parameters, I have found that using a single varchar works best. Then, instead of using an IN use a POSITION to determine whether or not the values match:

     execute my_macro( ('1 2 3');

     my_macro (parm_value varchar(150))
     SEL ...
     where POSITION(column_value IN :parm_value) > 0;

Whether you are using char or numeric data, the space separator is important. For instance, if you were checking states: exec my_macro ('HI AL') - without the space you would get Hawaii, Alabama and Iowa ('HIAL') since IA is Iowa. In the example above without the space you would get: 1,2,3,12,23 and 123. The downside for using this technique for numeric values is that the actual column value will need to be converted to char before the comparison can be made. If you are referencing thousands or more rows, this will slow the processing to a degree.

Hope this helps.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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