Contents x
Query Result To Scalar
  • Dark
    Light
Contents

Query Result To Scalar

  • Dark
    Light
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
Property Setting Description
Name String A human-readable name for the component.
Basic / Advanced Select Basic: 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.
Database Select The 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.
Schema Select The 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 Name Select The name of the table.
Table Columns Select The selected table's columns to be included in the query.
Order By Select The column to order by when running the query.
Sort Select Sort by either ascending or descending values.
Limit Integer A numeric value for the upper limit of rows to be loaded.
Filter Conditions Input Column Name Select an input column. Input columns depend on the chosen table and its selected columns.
Qualifier Is: 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.
Comparator Choose 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.
Value The value to be compared.
Combine Conditions Select Use the defined filters in combination with one another according to either AND or OR.
SQL Query Editor (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 Mapping Mapping Scalar 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
Property Setting Description
Name String A human-readable name for the component.
Basic / Advanced Select Basic: 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.
Schema Select The Redshift schema. The special value, [Environment Default], uses the schema that has been defined in the Matillion ETL environment.
Table Name Select The name of the table.
Table Columns Select The selected table's columns to be included in the query.
Order By Select The column to order by when running the query.
Sort Select Sort by either ascending or descending values.
Limit Integer A numeric value for the upper limit of rows to be loaded.
Filter Conditions Input Column Name Select an input column. Input columns depend on the chosen table and its selected columns.
Qualifier Is: 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.
Comparator Choose 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.
Value The value to be compared.
Combine Conditions Select Use the defined filters in combination with one another according to either AND or OR.
SQL Query Editor (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 Mapping Mapping Scalar 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
Property Setting Description
Name String A human-readable name for the component.
Basic / Advanced Select Basic: 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.
Project Select Select the BigQuery project. The special value, [Environment Default], uses the project defined in the Matillion ETL environment.
Dataset Select Select the BigQuery dataset. The special value, [Environment Default], uses the dataset defined in the Matillion ETL environment.
Table Select The name of the table.
Table Columns Select The selected table's columns to be included in the query.
Order By Select The column to order by when running the query.
Sort Select Sort by either ascending or descending values.
Limit Integer A numeric value for the upper limit of rows to be loaded.
Filter Conditions Input Column Name Select an input column. Input columns depend on the chosen table and its selected columns.
Qualifier Is: 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.
Comparator Choose 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.
Value The value to be compared.
Combine Conditions Select Use the defined filters in combination with one another according to either AND or OR.
SQL Query Editor (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 Mapping Mapping Scalar 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
Property Setting Description
Name String A human-readable name for the component.
Basic / Advanced Select Basic: 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.
Schema Select Select the table schema. The special value, [Environment Default], will use the schema defined in the environment.
Table Select The name of the table.
Table Columns Select The selected table's columns to be included in the query.
Order By Select The column to order by when running the query.
Sort Select Sort by either ascending or descending values.
Limit Integer A numeric value for the upper limit of rows to be loaded.
Filter Conditions Input Column Name Select an input column. Input columns depend on the chosen table and its selected columns.
Qualifier Is: 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.
Comparator Choose 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.
Value The value to be compared.
Combine Conditions Select Use the defined filters in combination with one another according to either AND or OR.
SQL Query Editor (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 Mapping Mapping Scalar 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

Property Setting Description
Name String A human-readable name for the component.
Basic / Advanced Select Basic: 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.
Catalog Select Select 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.
Database Select Select the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
Table Select The name of the table.
Table Columns Select The selected table's columns to be included in the query.
Order By Select The column to order by when running the query.
Sort Select Sort by either ascending or descending values.
Limit Integer A numeric value for the upper limit of rows to be loaded.
Filter Conditions Input Column Name Select an input column. Input columns depend on the chosen table and its selected columns.
Qualifier Is: 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.
Comparator Choose 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.
Value The value to be compared.
Combine Conditions Select Use the defined filters in combination with one another according to either AND or OR.
SQL Query Editor (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 Mapping Scalar Variable Name The name of a scalar variable to map.
Input Column Name A corresponding input column.
What's Next

Cookie consent

We use our own and third-party cookies to understand how you interact with our knowledge base. This helps us show you more relevant content based on your browsing and navigation history.