Derive allows you to enter simple expressions based upon columns within a table. For example, if you know that all values are positive or zero, the Derive Analysis can be used to add one to the column and take the natural logarithm of it. The Derive expression may be specified in a structured way as in the Variable Creation function, and may include any functions or operators supported by the Variable Creation function except a reference to another variable. It may also include free-formatted SQL text in all or part of an expression, making it possible to use constructs not supported by the expression builder. Of course, care should be taken in using this feature to create a valid expression, since validation is not performed on the SQL within the free-format text string.
Special handling is given to aggregation functions if they appear in the user-defined expression. Any requested aggregation function is computed over the entire input table (limited of course by the where clause if specified as an expert option) in one of the global aggregate derived tables shared by the other transformation functions. The aggregation is then treated as a constant in the user-defined expression. And although the user-defined expression may include ordered analytical functions, it may not include an aggregate within an ordered analytical function.