Skip to main content

[en] Blue icon with beaker full of bubbles. Formula In-DB Tool

[en] Use Formula In-DB 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.

[en] 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, visit In-Database Overview.

注意

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

[en] Configure the Tool

  • [en] Output Field: This is the field the formula will be applied to. Choose a field listed in the dropdown or add a new field.

  • [en] Type: Select the appropriate Data Type of the new field. If an existing field is selected, the Type is for reference only.

  • [en] 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.

  • [en] Navigationbuttons: Use the up or down arrows to set the order of formula execution. Select the "-" button to delete the selected formula.

  • [en] SQLExpression: Build the expression to be applied to the specified field.

    • [en] 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 is added to the Expression field at the top as it is being written.

    • [en] 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

  • [en] Insert Fields: A list of available columns that can be inserted in the SQL Expression editor.

  • [en] Validate expression here at runtime: Provides information about errors contained in the expression.

    • [en] When checked, a query is sent to the database and any errors are reported in the Messages display for the tool.

    • [en] 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.

: