Blue icon with beaker full of bubbles.

Formula In-DB Tool

Version:
Current
Last modified: October 21, 2019

The Formula In-DB tool creates or updates fields in an In-DB data stream with an expression using the native language of the database, such as SQL. Use the Formula In-DB tool to modify data within a database based on an expression or data relationship.

In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, see In-Database Overview.

While most In-DB tools do not require SQL commands, this tool requires SQL for more advanced processing.

Tool configuration

  • Output Field: This is the field the formula will be applied to. Choose a field listed in the drop down or add a new field.
  • Type: Select the appropriate Data Types of the new field. If an existing field is selected, the Type is for reference only.
  • Expression: The expression box is for reference only. It will populate with the expression built in SQL Expression. The formulas constructed here are embedded within a SELECT statement.
  • Navigation buttons: Click up icon or down icon to set the order of formula execution. Click delete icon to delete the selected formula.
  • SQL Expression: Build the expression to be applied to the specified field.

    • Building an expression:

      • Use the Insert Fields drop down to pick from available fields to construct your formula. Select a field name to add it to the expression field, or write a custom expression. The expression will be added to the Expression field at the top as it is being written.
      • A SELECT command is not required to write a CASE statement in the In-DB Formula tool. You can use the expression: CASE WHEN [FIELDNAME] = 'condition' THEN 'calculation'...END
  • Insert Fields: A list of available columns that can be inserted in the SQL Expression editor.
  • Validate expression here at runtime: Provides information about errors contained in the expression.
  • When checked, a query is sent to the database and any errors are reported in the Error icon Message display for the tool.
  • When unchecked, the query is not sent until the In-Database workflow is complete and any errors are reported in the Error icon Message display in any downstream tools
Was This Helpful?

Need something else? Visit the Alteryx Community or contact support.