Query Result To Scalar
    • Dark
      Light

    Query Result To Scalar

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake - Redshift - BigQuery - Synapse.

    Query Result To Scalar

    The Query Result To Scalar component enables users to write any custom SQL query that returns a scalar value. This value can then be mapped to an environment variable or job variable for use in other Matillion ETL functions.

    This component is the scalar equivalent of the Query Result To Grid component.

    Note

    Do not end SQL statements with a semicolon in this component.

    Properties

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic / AdvancedSelectBasic: this mode lets users edit each property of the query by manually clicking into the properties.
    Advanced: users are expected to write an SQL query in the query editor.
    DatabaseSelectThe Snowflake database. A database is a logical grouping of schemas. Each database belongs to a single Snowflake account. The special value, [Environment Default], uses the database that has been defined in the Matillion ETL environment. For more information, read Databases, Tables & Views.
    SchemaSelectThe Snowflake schema. A schema is a logical grouping of database objects (tables, views, etc.). Each schema belongs to a single database in Snowflake. The special value, [Environment Default], uses the schema that has been defined in the Matillion ETL environment.
    Table NameSelectThe name of the table.
    Table ColumnsSelectThe selected table's columns to be included in the query.
    Order BySelectThe column to order by when running the query.
    SortSelectSort by either ascending or descending values.
    LimitIntegerA numeric value for the upper limit of rows to be loaded.
    Filter ConditionsInput Column NameSelect an input column. Input columns depend on the chosen table and its selected columns.
    QualifierIs: Compares the column to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorChoose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null".
    "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than", will work only with numerics. The "Like" operator allows the wildcard character (%) to be used at the start and end of a string value to match a column. The "Null" operator matches only null values, ignoring whatever the value is set to.
    Not all data sources support all comparators, thus it is likely that only a subset of the above comparators will be available to choose from.
    ValueThe value to be compared.
    Combine ConditionsSelectUse the defined filters in combination with one another according to either AND or OR.
    SQL QueryEditor(Advanced Mode only) This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both Environment and Job variables are also listed in the bottom-left.
    SQL Queries can be written in the main panel and tested using the "Sample" button, which will display results below.
    Note: Do not add a trailing semi colon [;] to your SQL query.
    Scalar Variable MappingMappingScalar results from the SQL Query can be mapped to Environment and Job Variables.
    Returned scalars are available from the "Input Column Name" dropdown, and can be mapped to an Environment or Job variable in the Scalar Variable Name.

    Redshift Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic / AdvancedSelectBasic: this mode lets users edit each parameter of the query by manually clicking into the properties.
    Advanced: users are expected to write an SQL query in the query editor.
    SchemaSelectThe Redshift schema. The special value, [Environment Default], uses the schema that has been defined in the Matillion ETL environment.
    Table NameSelectThe name of the table.
    Table ColumnsSelectThe selected table's columns to be included in the query.
    Order BySelectThe column to order by when running the query.
    SortSelectSort by either ascending or descending values.
    LimitIntegerA numeric value for the upper limit of rows to be loaded.
    Filter ConditionsInput Column NameSelect an input column. Input columns depend on the chosen table and its selected columns.
    QualifierIs: Compares the column to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorChoose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null".
    "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than", will work only with numerics. The "Like" operator allows the wildcard character (%) to be used at the start and end of a string value to match a column. The "Null" operator matches only null values, ignoring whatever the value is set to.
    Not all data sources support all comparators, thus it is likely that only a subset of the above comparators will be available to choose from.
    ValueThe value to be compared.
    Combine ConditionsSelectUse the defined filters in combination with one another according to either AND or OR.
    SQL QueryEditor(Advanced Mode only) This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both Environment and Job variables are also listed in the bottom-left.
    SQL Queries can be written in the main panel and tested using the "Sample" button, which will display results below.
    Note: Do not add a trailing semi colon [;] to your SQL query.
    Scalar Variable MappingMappingScalar results from the SQL Query can be mapped to Environment and Job Variables.
    Returned scalars are available from the "Input Column Name" dropdown, and can be mapped to an Environment or Job variable in the Scalar Variable Name.

    BigQuery Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic / AdvancedSelectBasic: this mode lets users edit each property of the query by manually clicking into the properties.
    Advanced: users are expected to write an SQL query in the query editor.
    ProjectSelectSelect the BigQuery project. The special value, [Environment Default], uses the project defined in the Matillion ETL environment.
    DatasetSelectSelect the BigQuery dataset. The special value, [Environment Default], uses the dataset defined in the Matillion ETL environment.
    TableSelectThe name of the table.
    Table ColumnsSelectThe selected table's columns to be included in the query.
    Order BySelectThe column to order by when running the query.
    SortSelectSort by either ascending or descending values.
    LimitIntegerA numeric value for the upper limit of rows to be loaded.
    Filter ConditionsInput Column NameSelect an input column. Input columns depend on the chosen table and its selected columns.
    QualifierIs: Compares the column to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorChoose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null".
    "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than", will work only with numerics. The "Like" operator allows the wildcard character (%) to be used at the start and end of a string value to match a column. The "Null" operator matches only null values, ignoring whatever the value is set to.
    Not all data sources support all comparators, thus it is likely that only a subset of the above comparators will be available to choose from.
    ValueThe value to be compared.
    Combine ConditionsSelectUse the defined filters in combination with one another according to either AND or OR.
    SQL QueryEditor(Advanced Mode only) This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both Environment and Job variables are also listed in the bottom-left.
    SQL Queries can be written in the main panel and tested using the "Sample" button, which will display results below.
    Note: Do not add a trailing semi colon [;] to your SQL query.
    Scalar Variable MappingMappingScalar results from the SQL Query can be mapped to Environment and Job Variables.
    Returned scalars are available from the "Input Column Name" dropdown, and can be mapped to an Environment or Job variable in the Scalar Variable Name.

    Synapse Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic / AdvancedSelectBasic: this mode lets users edit each property of the query by manually clicking into the properties.
    Advanced: users are expected to write an SQL query in the query editor.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment.
    TableSelectThe name of the table.
    Table ColumnsSelectThe selected table's columns to be included in the query.
    Order BySelectThe column to order by when running the query.
    SortSelectSort by either ascending or descending values.
    LimitIntegerA numeric value for the upper limit of rows to be loaded.
    Filter ConditionsInput Column NameSelect an input column. Input columns depend on the chosen table and its selected columns.
    QualifierIs: Compares the column to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorChoose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null".
    "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than", will work only with numerics. The "Like" operator allows the wildcard character (%) to be used at the start and end of a string value to match a column. The "Null" operator matches only null values, ignoring whatever the value is set to.
    Not all data sources support all comparators, thus it is likely that only a subset of the above comparators will be available to choose from.
    ValueThe value to be compared.
    Combine ConditionsSelectUse the defined filters in combination with one another according to either AND or OR.
    SQL QueryEditor(Advanced Mode only) This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both Environment and Job variables are also listed in the bottom-left.
    SQL Queries can be written in the main panel and tested using the "Sample" button, which will display results below.
    Note: Do not add a trailing semi colon [;] to your SQL query.
    Scalar Variable MappingMappingScalar results from the SQL Query can be mapped to Environment and Job Variables.
    Returned scalars are available from the "Input Column Name" dropdown, and can be mapped to an Environment or Job variable in the Scalar Variable Name.

    Delta Lake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic / AdvancedSelectBasic: this mode lets users edit each property of the query by manually clicking into the properties.
    Advanced: users are expected to write an SQL query in the query editor.
    CatalogSelectSelect a Databricks Unity Catalog. The special value, [Environment Default], will use the catalog specified in the Matillion ETL environment setup. Selecting a catalog will determine which databases are available in the next parameter.
    DatabaseSelectSelect the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
    TableSelectThe name of the table.
    Table ColumnsSelectThe selected table's columns to be included in the query.
    Order BySelectThe column to order by when running the query.
    SortSelectSort by either ascending or descending values.
    LimitIntegerA numeric value for the upper limit of rows to be loaded.
    Filter ConditionsInput Column NameSelect an input column. Input columns depend on the chosen table and its selected columns.
    QualifierIs: Compares the column to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorChoose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null".
    "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than", will work only with numerics. The "Like" operator allows the wildcard character (%) to be used at the start and end of a string value to match a column. The "Null" operator matches only null values, ignoring whatever the value is set to.
    Not all data sources support all comparators, thus it is likely that only a subset of the above comparators will be available to choose from.
    ValueThe value to be compared.
    Combine ConditionsSelectUse the defined filters in combination with one another according to either AND or OR.
    SQL QueryEditor(Advanced Mode only) This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both Environment and Job Variables are also listed in the bottom-left.
    SQL Queries can be written in the main panel and tested using the Sample button, which will display results below.
    Note: Do not add a trailing semi colon [;] to your SQL query.
    Scalar Variable MappingScalar Variable NameThe name of a scalar variable to map.
    Input Column NameA corresponding input column.