Archives of the TeradataForum
Message Posted: Fri, 20 Aug 2004 @ 08:11:17 GMT
| 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
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
position (' ' || trim(column_name) || ' '
in ' ' || :parm || ' ') > 0;