Run-time Subsetting

Section - TM Language Reference

This is only of use in relational systems and should not be confused with the design-time subsetting available in the TM Design Tool.

By default, defining a Transform from a table called X will eventually result in a SQL query being issued in the form:

select * from X

This is, of course, the right query to issue if you want to examine all X items and then perform a transformation. The system will systematically iterate all X values.

However, consider this Transform:

TRANSFORM Y <- X;

IF (customer = getstring(‘supplied customer name’) ) THEN

CONSTRUCT

. . . -- actually do something with row

END_IF;

then you would have a long wait if your database contains 10,000,000 rows.

What the above code is really saying is take each row and look for a string match with customer. When you find a match, then go on to transform it. You may have a long wait if your database contains 10,000,000 rows.

To prevent this from happening, Transformation Manager will automatically turn suitable outer inclosing IF Blocks into true SQL select statements. For this to happen

The IF conditional must not include complex logic
Only one CONSTRUCT must be present in any particular code block
each statement must include only inequality, equality, greater or less than operators which may be combined only with the AND operator
conditional tests are for scalar values against a fixed values OR a values obtained via GETSTRING.
conditional tests tell you that the IF statement has not been condensed.

Notes:

SML attaches no special significance to SQL wild card characters
The system will warn if it cannot do the necessary conversions.
Entering text directly into the advanced clause may also be used to produce efficient run-time subsetting.
It is possible to define the original select by adding a SQL where clause into the Advanced field of the Transform | Properties dialog. This will be concatenated with the original select. Thus adding the string WHERE attr like 2% will change the select into:

select * from X WHERE attr like 2%

This technique allows more flexible SQL statements to be added. (including ORDER BY clauses).
There is no need to add such subclasses to relationships or dependent Transforms. These are always only called as needed.

WARNING:

Apart from value substitution using GETSTRING, all other items in the Advanced field are totally unchecked and will be generated as SQL92 statements. The system will report a run-time error if incorrect text is entered in the Advanced field.