Blue icon with beaker full of bubbles.

Formula In-DB Tool

Version:
Current
Last modified: May 11, 2020

Use the Formula In-DB tool to create or update fields in an In-DB data stream with an expression using the native language of the database, like 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.

Configure the Tool

  • Output Field: This is the field the formula will be applied to. Choose a field listed in the dropdown or add a new field.
  • Type: Select the appropriate Data Type 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 populates with the expression built-in SQL Expression. The formulas constructed here are embedded within a SELECT statement.
  • Navigation buttons: Use the up or down arrows to set the order of formula execution. Select the "-" button to delete the selected formula.
  • SQL Expression: Build the expression to be applied to the specified field.

    • Use the Insert Fields dropdown 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 Messages 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 Messages display in any downstream tools
Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.