Home Page for the TeradataForum

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
        position (' ' || trim(column_name)  || ' '
                  in ' ' || :parm || ' ') > 0;


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023