Masking commas within a macro parameter

Database
Fan

Masking commas within a macro parameter

Hi,

I was wondering if anyone knows a simple way of masking a comma within a given macro parameter so that Teradata doesn't interpret the parameter as multiple parameters.

eg:
CREATE MACRO test(prodno) AS
(
SELECT *
FROM products
WHERE product_code IN (:prodno)
;
);

EXEC test(12, 13);

The number of products could be one or more (ie a variable number).

Obviously the macro and execution, as is, produces an error as Teradata is only expecting one parameter, but sees two.

Is there a simple way around this?

Thanks in advance for your help.

Cheers,
Scott.

17 REPLIES
Enthusiast

Re: Masking commas within a macro parameter

I don't know if this is a good solution, but now that it sprouted up, I might as well throw it....

(0) Create a stored proc that accepts a string as argument (say a string like '12,13' ). Use it to generate and execute a dynamic SQL which would do an insert into a volatile table.

(1) Create a volatile table in the session.

(2) Call the procedure with the required "string" argument.

(3) Read the results set from the volatile table as in a simple select.
Senior Apprentice

Re: Masking commas within a macro parameter

Hi Joe,
you dothis is a similar approach:

http://www.teradataforum.com/teradata/20020719_121740.htm

Dieter
Enthusiast

Re: Masking commas within a macro parameter

I had written a stored procedure to process parameters few months back.
I have not maintained this for a while, but may be a good starting point.

REPLACE PROCEDURE sysdba.PR_Process_Parm (
IN var1 VARCHAR(100)
,OUT msg VARCHAR(4000))

BEGIN

DECLARE sql_text VARCHAR(1000);
DECLARE no_of_parm int;
DECLARE xfer_parm VARCHAR(2000);
DECLARE process_parm VARCHAR(2000);

DECLARE l_db_nme VARCHAR(100);
DECLARE cnt int;

SET process_parm = var1;

SET xfer_parm = '';
SET sql_text= '';
SET no_of_parm = 1;

-- Find the number of parameters
WHILE INDEX(process_parm, ',') > 0
DO
IF INDEX(process_parm, ',') >= 1 THEN

SET no_of_parm = no_of_parm +1;
SET process_parm = trim(both from substr(process_parm,INDEX(process_parm, ',')+1, character_length(process_parm)));

ELSEIF INDEX(process_parm, ',') = 0 THEN

SET no_of_parm = no_of_parm +1;

END IF;
END WHILE;

SET process_parm = var1;

-- Process the parameters
WHILE no_of_parm > 0
DO
IF no_of_parm >1 THEN

SET xfer_parm = xfer_parm || '''' || substr(process_parm, 1, INDEX(process_parm, ',')-1) || '''' || ',' ;
SET process_parm = trim(both from substr(process_parm,INDEX(process_parm, ',')+1, character_length(process_parm)));
SET no_of_parm = no_of_parm -1;

ELSEIF no_of_parm = 1 THEN

SET xfer_parm = xfer_parm || '''' || process_parm || '''' ;
SET no_of_parm = no_of_parm -1;

END IF;

END WHILE;

SET xfer_parm = '(' || xfer_parm || ')';

SET sql_text = '';
SET sql_text = 'insert into sysdba.tmp sel * ' || X'0D';
SET sql_text = sql_text || 'from sysdba.tmp1 ' || X'0D';
SET sql_text = sql_text || 'where dbname IN ' || X'0D';
SET sql_text = sql_text || xfer_parm || X'0D';
SET sql_text = sql_text || ' ;' || X'0D';

CALL DBC.SYSEXECSQL (:SQL_TEXT);
SET msg = xfer_parm ;

END;
Fan

Re: Masking commas within a macro parameter

Thanks to everyone for their responses. It appears there is no simple masking function, but I've avoided the problem using your suggestions.

Much appreciated.

Cheers,
Scott.

Re: Masking commas within a macro parameter

Let me get this straight....This cannot be done without writing a stored procedure? Is the macro completely out of the picture?
Enthusiast

Re: Masking commas within a macro parameter

There are always a bit of "dirty code" that can be written... (atleast for academic purposes)

CREATE MACRO RECMAC(VALS VARCHAR(100))
AS
(

WITH RECURSIVE EXPRN (VALU, RMSTRING )
AS
(
SELECT (TRIM(CASE POSITION(',' IN STRN)
WHEN 0 THEN STRN
ELSE SUBSTRING(STRN FROM 1 FOR POSITION(',' IN STRN)-1)
END
)) (INTEGER)
, TRIM(
CASE POSITION(',' IN STRN)
WHEN 0 THEN NULL
ELSE SUBSTRING(STRN FROM POSITION(',' IN STRN)+1)
END
)
FROM
(
SELECT :VALS
)X (STRN)

UNION ALL

SELECT (TRIM(CASE POSITION(',' IN RMSTRING)
WHEN 0 THEN RMSTRING
ELSE SUBSTRING(RMSTRING FROM 1 FOR POSITION(',' IN RMSTRING)-1)
END
)) (INTEGER)
, TRIM(
CASE POSITION(',' IN RMSTRING)
WHEN 0 THEN NULL
ELSE SUBSTRING(RMSTRING FROM POSITION(',' IN RMSTRING)+1)
END
)
FROM EXPRN
WHERE RMSTRING IS NOT NULL

)

SELECT *
FROM products
WHERE product_code IN
(SELECT VALU FROM EXPRN)

;

);

EXEC RECMAC('12,43,33,55,1234,8934, 66, 132123 , 54 ');

(note that everything is given as a single argument string literal in quotes

Re: Masking commas within a macro parameter

Dude, thats like the coolest thing i've ever seen in my life. Thanks a bunch! Cognos is giving me a pain this week!
rgs
Enthusiast

Re: Masking commas within a macro parameter

I like Joe’s solution. Very clever! Here is a stored procedure solution if you are on TD 12.0 using dynamic result sets:

CREATE PROCEDURE testsp(in vlus VARCHAR(100))
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE stmt_str VARCHAR(500);
DECLARE response1 CURSOR WITH RETURN ONLY TO CLIENT FOR stmt1;

SET stmt_str = 'SELECT * FROM products WHERE product_code IN (' || vlus || ');';

PREPARE stmt1 FROM stmt_str;
OPEN response1;
END;

BTEQ -- Enter your DBC/SQL request or BTEQ command:
call testsp('3,4');

*** Procedure has been executed.
*** Warning: 3212 The stored procedure returned one or more result sets.
*** Total elapsed time was 1 second.

*** ResultSet# 1 : 2 rows returned by "RGS.TESTSP".

product_code product_name
------------ --------------------
3 hose
4 car

Rolf
Enthusiast

Re: Masking commas within a macro parameter

JOE:

How to deal with macro with input with , and ''

Say

create macro macro_name(input type) as

column_value in ('value_1', 'value_2')

USE one input to represent 'value_1', 'value_2'

Many thanks,