All Forums Database
Scott 3 posts Joined 03/07
29 Mar 2007
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 productsWHERE 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.

joedsilva 505 posts Joined 07/05
30 Mar 2007

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.

dnoeth 4628 posts Joined 11/04
30 Mar 2007

Hi Joe,you dothis is a similar approach:http://www.teradataforum.com/teradata/20020719_121740.htmDieter

Dieter

BBR2 96 posts Joined 12/04
30 Mar 2007

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;

Scott 3 posts Joined 03/07
02 Apr 2007

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.

lcampbell 2 posts Joined 02/08
26 Feb 2008

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

joedsilva 505 posts Joined 07/05
26 Feb 2008

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 productsWHERE 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

lcampbell 2 posts Joined 02/08
27 Feb 2008

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 106 posts Joined 02/07
27 Feb 2008

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 1BEGIN 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 carRolf

georgexu 7 posts Joined 02/08
27 Mar 2008

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,

joedsilva 505 posts Joined 07/05
07 Apr 2008

You must be able to make use of the same code, the only change I foresee is to change the cast (INTEGER) with (VARCHAR(30)) or something appropriate.and you can execute the macroexec macroname('value1,value2,value3');note the lack of single quotes for individual values ...

yphillips 4 posts Joined 04/08
15 Apr 2008

Is there a way to use the code to allow for multiple parameters with multiple values? CREATE MACRO RECMAC(VALS VARCHAR(100))AS(WITH RECURSIVE EXPRN (VALU, RMSTRING )AS(SELECT (TRIM(CASE POSITION(',' IN STRN)WHEN 0 THEN STRNELSE SUBSTRING(STRN FROM 1 FOR POSITION(',' IN STRN)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN STRN)WHEN 0 THEN NULLELSE SUBSTRING(STRN FROM POSITION(',' IN STRN)+1)END)FROM(SELECT :VALS)X (STRN)UNION ALLSELECT (TRIM(CASE POSITION(',' IN RMSTRING)WHEN 0 THEN RMSTRINGELSE SUBSTRING(RMSTRING FROM 1 FOR POSITION(',' IN RMSTRING)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN RMSTRING)WHEN 0 THEN NULLELSE SUBSTRING(RMSTRING FROM POSITION(',' IN RMSTRING)+1)END)FROM EXPRNWHERE RMSTRING IS NOT NULL)SELECT *FROM productsWHERE product_code IN (SELECT VALU FROM EXPRN););

joedsilva 505 posts Joined 07/05
15 Apr 2008

That's going to be extruciatingly painful, perhaps with the help of a bunch of GT tables and all that recursive stuff,we must be able to achieve it...But what could be a practical use for it ?

yphillips 4 posts Joined 04/08
16 Apr 2008

I am trying to pass multiple parameters into a macro that could have multiple values. I tried to accomplish this task by using the code you posted, but I continue to get "3707:Syntax error, expected something like a 'Select' keyword or '('between')' and the 'With' keyword. Here is sample code:Replace MACRO database.get_data(Opco_code VARCHAR(100), Div_code VARCHAR(100), Group_code VARCHAR(100))AS(WITH RECURSIVE EXPRN (VALU, RMSTRING )AS(SELECT (TRIM(CASE POSITION(',' IN STRN)WHEN 0 THEN STRNELSE SUBSTRING(STRN FROM 1 FOR POSITION(',' IN STRN)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN STRN)WHEN 0 THEN NULLELSE SUBSTRING(STRN FROM POSITION(',' IN STRN)+1)END)FROM(SELECT : Opco_Code )X (STRN)UNION ALL SELECT (TRIM(CASE POSITION(',' IN RMSTRING)WHEN 0 THEN RMSTRINGELSE SUBSTRING(RMSTRING FROM 1 FOR POSITION(',' IN RMSTRING)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN RMSTRING)WHEN 0 THEN NULLELSE SUBSTRING(RMSTRING FROM POSITION(',' IN RMSTRING)+1)END)FROM EXPRNWHERE RMSTRING IS NOT NULL ) /************Second Parm*******/WITH RECURSIVE EXPRN2 (VALU2, RMSTRING2 )AS(SELECT (TRIM(CASE POSITION(',' IN STRN2)WHEN 0 THEN STRN2ELSE SUBSTRING(STRN2 FROM 1 FOR POSITION(',' IN STRN2)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN STRN2)WHEN 0 THEN NULLELSE SUBSTRING(STRN2 FROM POSITION(',' IN STRN2)+1)END)FROM(SELECT : Div_Code)X (STRN2)UNION ALLSELECT (TRIM(CASE POSITION(',' IN RMSTRING2)WHEN 0 THEN RMSTRING2ELSE SUBSTRING(RMSTRING2 FROM 1 FOR POSITION(',' IN RMSTRING2)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN RMSTRING2)WHEN 0 THEN NULLELSE SUBSTRING(RMSTRING2 FROM POSITION(',' IN RMSTRING2)+1)END)FROM EXPRN2WHERE RMSTRING2 IS NOT NULL)*****Third Parm***** WITH RECURSIVE EXPRN3 (VALU3, RMSTRING3 )AS(SELECT (TRIM(CASE POSITION(',' IN STRN3)WHEN 0 THEN STRN3ELSE SUBSTRING(STRN3 FROM 1 FOR POSITION(',' IN STRN3)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN STRN3)WHEN 0 THEN NULLELSE SUBSTRING(STRN3 FROM POSITION(',' IN STRN3)+1)END)FROM(SELECT : Group_Code )X (STRN3)UNION ALLSELECT (TRIM(CASE POSITION(',' IN RMSTRING3)WHEN 0 THEN RMSTRING3ELSE SUBSTRING(RMSTRING3 FROM 1 FOR POSITION(',' IN RMSTRING3)-1)END )) (INTEGER), TRIM( CASE POSITION(',' IN RMSTRING3)WHEN 0 THEN NULLELSE SUBSTRING(RMSTRING3 FROM POSITION(',' IN RMSTRING3)+1)END)FROM EXPRN3WHERE RMSTRING3 IS NOT NULL)SELECT distinct Buyer_code, Buyer_name FROM database.deptWHERE Opco_code IN (SELECT VALU FROM EXPRN)And Division_code IN (SELECT VALU2 FROM EXPRN2);And Group_code IN (SELECT VALU3 FROM EXPRN3););Thanks for your help.....

joedsilva 505 posts Joined 07/05
17 Apr 2008

A recursive sql is incomplete with out a selectsoyour recursive query should be likeWITH RECURSIVE MYRECTBL(a, b, c)(........)SELECT a, b, x, y FROM MYRECTBL JOIN ANOTHERTBLON a = x;in your macro, you are defining multiple recursive queries but none of them have a select.you should change the code to (a) have an insert in the begining of each rec query to a temp table (b) end each rec query with a select from rec tbl (and a semicolon)And your final select should join with those temp tables.

yphillips 4 posts Joined 04/08
18 Apr 2008

I tried your suggestions but I continue to get the message that INTO is not allowed. Is it possible for you to use the code I posted previously as an example to explain where the INTO logic should be placed?

joedsilva 505 posts Joined 07/05
19 Apr 2008

you cannot have INTO clauses in macros, they can be used in stored procs. how ever, you can't have recursion in stored procs...here's my suggestion ...Replace MACRO database.get_data(Opco_code VARCHAR(100),Div_code VARCHAR(100),Group_code VARCHAR(100))AS(-- first rec query, insert into a global temp tableINSERT INTO EXPRN1_GTWITH RECURSIVE EXPRN1 (VALU, RMSTRING )AS(........)-- this select is a MUST, the output of this select goes to the GT tableSELECT VALU, RMSTRINGFROM EXPRN1WHERE ......;-- second rec query, insert into another global temp tableINSERT INTO EXPRN2_GTWITH RECURSIVE EXPRN2 (VALU, RMSTRING )AS(........)-- this select is a MUST, the output of this select goes to the GT tableSELECT VALU, RMSTRINGFROM EXPRN2WHERE ......;-- third rec query, insert into a global temp tableINSERT INTO EXPRN3_GTWITH RECURSIVE EXPRN3 (VALU, RMSTRING )AS(........)-- this select is a MUST, the output of this select goes to the GT tableSELECT VALU, RMSTRINGFROM EXPRN3WHERE ......;-- the final select, making use of all GT tables SELECT distinct Buyer_code, Buyer_name FROM database.deptWHERE Opco_code IN (SELECT VALU FROM EXPRN1_GT)And Division_code IN (SELECT VALU FROM EXPRN2_GT);And Group_code IN (SELECT VALU FROM EXPRN3_GT);-- macro ends here );Now it's very important that you understand that you can't jave multile recursive structures in a single sql statement. so if you see the sample code above, I have written three separate stand alone recursive queries which populate three different global temporary tables (As a matter of fact, if the data types are the same, you can have just one GT, but I'll not try to add that complication now, let's get the basics to work).And in the final select I am using those three global temporary tables to join with the table I need the data to be pulled out of ...As a first step I would recommend you to get one of those recurisve queries to work (ie insert into a GT, plus select from database.dept using that GT).You can add more recursive queries one you get comfortable with that ...

jammy19031989 8 posts Joined 07/13
29 Mar 2016

replace MACRO FBA_M.TEST_OCTOPUStest1 (p1 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 :p1 )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 a, b, c, sum(amt)  FROM viewdb.x 

WHERE a IN

(SELECT VALU 

FROM EXPRN) group by 1,2,3 ;);

 

Note: Here column "a" is a varchar column.

 

when trying to execute,

 

EXEC FBA_M.TEST_OCTOPUStest1('SUSG05482,SUS002266');

 

says: Teradata Error 2620: The format or Data contains a bad character.

 

please help me on this!!

 

Thanks

 

You must sign in to leave a comment.