LIKE statement in a macro

[Rbar]

I'm having trouble getting the syntax for a like statemetn to use paremeters in a macro. I can't get the paremeters to work. Can anyone se what I'm missing?

replace macro MACRO_DB.CollStatsPT_M (DBname Char (50), indicator Char (12))
as
(
SELECT databasename, tablename
'FROM
DBC.Tables
WHERE
DatabaseName like any ('%:indicator%','Bname')
ORDER By databasename, tablename

 

[dnoeth]

1. Indicator is a reserved word.

2. Don't quote the parameters, use
'%' || :indic || '%'

3. Don't know if it's documented anywhere, that you can't concatenate when using ANY/ALL, but
DatabaseName like any ('%' || :indic || '%',Bname)
returns a 3707 Syntax error in 05.00.00.17

4. Even if there was no error, it wouldn't return the expected result set, because of differences between = 'xx' and like 'xx' regarding Chars and trailing blanks.
DBname Char(50) will not work but DBname Char(30) will.
So it's better to change it to an ORed condition:
replace macro CollStatsPT_M (DBname Char (50), indic Char (12))
as
(
SELECT databasename, tablename
FROM
DBC.Tables
WHERE
DatabaseName like '%' || :indic || '%'
OR DatabaseName = Bname
ORDER By databasename, tablename;
);

5. You know what happens when indic is an empty string?

Dieter

 

[Rbar]

This construct worked:
'%'||trimdbname1)||'%'
the like % % with a space in the parameter entry isn't very selective.