All Forums Analytics
jimturn7ts 1 post Joined 01/15
09 Jan 2015
Passing a string into a macro for use in a IN statement

Hello,
 
I've set a macro which i'd like to pass a string into to use in a in statement.
 
So for example.  my query code snipet reads -
where item_nbr in :my_string  (where item_nbr is a decimal)
and i am passing in
exec my_marco(my_string='(1,2,3,4,5)').
I'm having real difficulty with this, as in the various ways i have tried, my_string is not recognised as a list of decimals, or numbers.....or the comma's seperating the values in my_string think i'm trying to pass 5 variables into the macro, rather than 1 variable of 5 values.
Can anyone help me pass in this string (which should be recognised as a list of decimals) into my select query?
 
Hope you can help!
 

tomnolan 594 posts Joined 01/08
09 Jan 2015

Unfortunately, what you want to do is not possible.
 
You can use a single macro argument as a single value for the right side of the IN predicate, but you cannot use a single macro argument to represent a list of values for the right side of the IN predicate.
 
The following are all legal:
 
replace macro mymac (p1 integer) as (select ErrorCode, ErrorText from DBC.ErrorMsgs where ErrorCode = :p1 order by 1 ; ) ;
execute mymac(3032);
Returns:
3,032 User password has expired.
 
replace macro mymac (p1 integer) as (select ErrorCode, ErrorText from DBC.ErrorMsgs where ErrorCode in :p1 order by 1 ; ) ;
execute mymac(3032);
Returns:
3,032 User password has expired.
 
replace macro mymac (p1 integer, p2 integer, p3 integer) as (select ErrorCode, ErrorText from DBC.ErrorMsgs where ErrorCode in (:p1, :p2, :p3) order by 1 ; ) ;
execute mymac(3032,3996,6706);
Returns:
3,032 User password has expired.
3,996 Right truncation of string data.
6,706 The string contains an untranslatable character.
 

tomnolan 594 posts Joined 01/08
09 Jan 2015

As a workaround, you can use the STRTOK_SPLIT_TO_TABLE function to split a character literal containing comma-separated values into a derived table, and then use the derived table as the right side of the IN predicate.
 
The STRTOK_SPLIT_TO_TABLE function is available beginning with Teradata Database 14.0.
 
replace macro mymac (p1 varchar(100)) as (select ErrorCode, ErrorText from DBC.ErrorMsgs where ErrorCode in (select cast(dt.token as integer) from table (strtok_split_to_table (1, :p1, ',') returns (outkey integer, tokennum integer, token varchar(100) character set unicode)) as dt) order by 1 ; ) ;
execute mymac('3032,3996,6706');
Returns:
3,032 User password has expired.
3,996 Right truncation of string data.
6,706 The string contains an untranslatable character.
 

dnoeth 4628 posts Joined 11/04
09 Jan 2015

You can't pass a list of values as a parameter, you need Dynamic SQL which is only available in SPs.
But in TD14 you can utilize STRTOK_SPLIT_TO_TABLE:

WHERE item_nbr IN 
 (
   SELECT CAST(token AS INT)
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, :my_string, ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(20) CHARACTER SET UNICODE)
              ) AS d 
 )


EXEC my_marco(my_string='1,2,3,4,5')

 

Dieter

jammy19031989 8 posts Joined 07/13
28 Mar 2016

Hi,

I have landed in the same situation.

replace MACRO TEST (p1 VARCHAR(100) ) AS
(
SELECT a, b, c sum(k) FROM viewdb.x
WHERE a IN
(
SELECT CAST(d.token AS INT)
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, :p1, ',')
RETURNS (outkey INTEGER,
tokennum INTEGER,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d
) group by 1,2,3;);

execute mymac('3032,3996,6706');

the column 'a' is a varchar(50) column.

error 2620: The format or data contains a bad character.

or is this issue due to selecting from view?

kindly reply as early as possible.
thanks

dnoeth 4628 posts Joined 11/04
29 Mar 2016

If column a is a varchar there's no need to CAST(d.token AS INT).

Dieter

jammy19031989 8 posts Joined 07/13
29 Mar 2016

Thank you so much for the Quick response!! you saved my day!!
 
Cheers!!

jammy19031989 8 posts Joined 07/13
01 Apr 2016

But I am in need to pass some parameter value that has comma in the middle of characters. 
I am trying to escape paranthesis but it is not working. 
I am trying like this 
execute mymac('3032,'"39,96"',6706');
the value "39,96" is having comma in its value.
kindly do the needful. thanks!
 
 
 
 

dnoeth 4628 posts Joined 11/04
01 Apr 2016

Why don't you simply change the delimiter character to any other character which is not part of your data?

Dieter

You must sign in to leave a comment.