Archives of the TeradataForum

Message Posted: Fri, 20 Aug 2004 @ 08:11:17 GMT


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

Subj:		Re: Arrays in macros

From:		Dieter Noeth

Michael Larkins wrote:

I think I have a little bit better idea of your need. Although you cannot dynamically pass a different number of parameters, you can pass a single parameter of a varying length. Try something like this:

          cm passer (parm varchar(200)) AS
          (sel * from mytable_name
          where position (column_name in :parm) > 0;)

Note: if column_name is a char or varchar column this works fine. However, if it is a different datatype, you will need to convert it to character: position(cast(column_name AS char(<length_goes_here>)) in :parm) > 0

The trick here is to submit all your values in a single string with a delimiter between them. For instance:

          EXEC passer ('12 5460 6717 7908 110234');

This execution would find rows with these five different values.

No, it will find no rows at all.

If column_name is casted to a char (implicit/explicit) this will result in an automatic typecast, i.e. right aligned with leading blanks.

E.g. 12 will be casted to ' 12'.

To solve that problem column_name has to be trimmed/casted to a varchar, but this will return to many rows, e.g. '12' matches now 1 and 2 also.

To solve that problem the delimiting character has to be added to trim(column_name) and the parm:

     replace macro passer (parm varchar(200)) AS
     (
     select *
     from mytable_name
     where
        position (' ' || trim(column_name)  || ' '
                  in ' ' || :parm || ' ') > 0;
     );

Dieter


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

Attachments

Library

Quick Reference