Combining Parameters of a Macro

Database

Combining Parameters of a Macro

Hi all,

Is there any way to combine two Parameters of a macro as a single string during runtime so that it can be searched using like clause?

For ex i had created a macro and coded as LIKE :Dbname||'.'||:Tbname;in the last line

the macro is compiling but it is not producing result is there any better way to do it?

REPLACE MACRO DD_DWUTILS.Macronames (DBname char(30),Tbname char(30),Tablekind char(1))

AS

(

INSERT INTO DD_MEDW.table1

(col1 ,

col2,

Col3,

Col4,

Col5)

SELECT x,y,:Dbname,:Tbname,:Tablekind

FROM DBC.tables

WHERE TABLEKIND='V'

AND REQUESTTEXT LIKE :Dbname||'.'||:Tbname;

);
4 REPLIES
Teradata Employee

Re: Combining Parameters of a Macro

I suggest:

a) Change the macro parameters from Char to VarChar. Otherwise use TRIM to remove the trailing spaces.
b) Apply "NOT CASESPECIFIC" if using ANSI session mode.

Re: Combining Parameters of a Macro

Hi shaw ,

Thanks for your suggestion.i tried your inputs but no luck..have a doubt whether it is madatory to have strings to be within quotes when using Like operator.?If it is so why the Macro is getting compiled eventhough i am not coding the Parameter with in the Quotes using like operator?
N/A

Re: Combining Parameters of a Macro

What about adding some wildcards?

;-)

REQUESTTEXT LIKE '%' || TRIM(:Dbname) || '.' || TRIM(:Tbname) || '%';

Dieter

Re: Combining Parameters of a Macro

Hi Dieter,

It Works!!!!!

That was a Valuable Trick....

Thanks.