Passing a string into a macro for use in a IN statement

Analytics

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!

8 REPLIES
Teradata Employee

Re: Passing a string into a macro for use in a IN statement

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.

Teradata Employee

Re: Passing a string into a macro for use in a IN statement

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.

Senior Apprentice

Re: Passing a string into a macro for use in a IN statement

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')
Enthusiast

Re: Passing a string into a macro for use in a IN statement

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

Senior Apprentice

Re: Passing a string into a macro for use in a IN statement

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

Enthusiast

Re: Passing a string into a macro for use in a IN statement

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

Cheers!!

Enthusiast

Re: Passing a string into a macro for use in a IN statement

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!

Senior Apprentice

Re: Passing a string into a macro for use in a IN statement

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