Guidelines for using custom macros

Keep the following guidelines in mind when creating or using a custom macro:

The name of a custom macro must be alphanumeric. You cannot use spaces in the name string, but you can use underscores (_).

If a data source has been configured with the property ENABLE_SELECT_SORT_BY = TRUE, then you must write raw SQL custom macros with an ORDER BY clause in order to sort the returned records by the audience key fields of the audience level under which you are working. Otherwise, if the sort order is not as expected, an error will be generated when the custom macro is used in a derived field in a Snapshot process.

If you do not compare a returned value from a custom macro, if the value is numeric, non-zero values are treated as TRUE (and therefore IDs associated with them are selected) and zero values are treated as FALSE. String values are always treated as FALSE.

When creating a custom macro that uses raw SQL, using a temp table can greatly speed up the performance of the raw SQL by scoping the amount of data it needs to work with.

When a custom macro uses temp tables in its underlying logic, a temp table will be forced up to the database so that the logic does not fail.

However, if a custom macro is used in a top level SELECT, then there is no history for Campaign to use to force a temp table up to the database, and the logic fails.

Thus, when creating a custom macro that uses raw SQL, you might need to create two versions of the same custom macro - one that uses temp table tokens and one that does not.

The custom macro without temp table tokens can be used at the top of a tree (for example, in the first SELECT). The one with temp table tokens can be used anywhere else in the tree when there might be a temp table to take advantage of.

Self-joins might occur when combining values returned from custom macros when querying against unnormalized data, which is not likely to be the desired behavior.

For example, if you a use a custom macro based on raw SQL that returns a value and (in a Snapshot process, for example) you output the custom macro and another field from the table that the custom macro is based on, Campaign performs a self join on that table. If the table is non-normalized, you will end up with a Cartesian product (that is, the number of records displayed is more than you would expect).

Custom macros are now automatically by reference, because the definition of the custom macro is not copied into the current process.

At execution time, a custom macro is resolved by looking up its definition in the UA_CustomMacros system table (where definitions are stored) and then used/executed.

Unlike stored queries, custom macro names must be unique, independent of the folder path. In releases prior to 5.0, you could have a stored query named A, for example, in both folder F1 and F2.

Campaign supports stored queries from earlier releases. However, references to non-unique stored queries must use the old syntax:

storedquery(<query name>)

When resolving user variables in custom macros, Campaign uses the current value of the user variable when checking syntax. If the current value is left blank, Campaign generates an error.

The temp table token is provided as a performance optimization advanced feature that scopes the amount of data pulled down from the database by the set of audience IDs in the temp table available for use by the current process. This temp table list of IDs might be a superset of the IDs in the current cell. Therefore, aggregate functions performed over the temp table (for example, average or sum) are not supported and might generate incorrect results.

If you intend to use the custom macro across several different databases, you might want to use a IBM® expression rather than raw SQL, since raw SQL can be specific to a particular database.

If a custom macro contains raw SQL and another custom macro, the custom macro is resolved, executed and its value returned before the raw SQL is executed.

Campaign treats a comma as a parameter separator. If you are using commas as literal characters in a parameter, enclose the text in open and close brackets ({}), as in the following example:

TestCM( {STRING_CONCAT(UserVar.Test1, UserVar.Test2) } )

Campaign supports simple substitution for parameters in custom macros using raw SQL code. For example, if you set up a Select process box on a flowchart containing this query:

exec dbms_stats.gather_table_stats(tabname=> <temptable>,ownname=> 'autodcc')

Campaign would successfully substitute the actual temp table in place of the <temptable> token. Note that the single quotes around the table name are required.

The following tables show how Campaign treats custom macros in queries and derived fields.

Custom macros in queries and derived fields (Select, Segment, and Audience Processes)

Type of custom macro	How it is used
Raw SQL: IDs	Runs as a separate query. The ID list is merged with other results. If a custom macro contains other custom macros plus raw SQL, the custom macros are resolved and executed and then the raw SQL is executed.
Raw SQL: IDs + Value	Expects that the returned value will be used in an expression or as a comparison. If the value is not used this way, Campaign treats a non-zero value as TRUE for ID selection and a zero value and string as FALSE.
IBM® Expression	The expression is resolved and a syntax check is performed. One query per table is supported, and the IDs are match/merged.

In a Raw SQL Query (Select, Segment, and Audience Processes)

Type of custom macro	How it is used
Raw SQL: IDs	Custom macro is resolved and then the query is executed.
Raw SQL: IDs + Value	Not supported.
IBM® Expression	The expression is resolved, but no syntax check is performed. If the expression is incorrect, it is detected by the database server when executed.