Tables: Run SQL query on data table and return data table

Use this action to perform a SQL query on a data table, and output a new data table.

SQL is a common, standard language used to retrieve and manipulate databases or tables. A SQL query, like SELECT fruit, SKU FROM currentstock WHERE quantity>=20 is like saying “Look at the currentstock table, and give me the fruit name and SKU for any rows where the quantity is greater or equal to 20” in a single query.

SQL actions are great as a complement to other Catalytic table actions. Use SQL actions for work like complex filtering, sorting, or grouping; and use actions like Tables: Add a row or CSV: Add or remove columns for manipulating the data structure.

How to use SQL queries

SQL is an easy way to start writing powerful queries on your data—you can write helpful, basic SQL queries in just a few minutes. There are many commonly used SQL queries that are practical for all users, you can see some of them in the Commonly used queries section below.

To create your own custom queries, you can try modifying some of the examples below. For more complex queries, it’s necessary to learn the SQL language. See w3schools SQL Tutorial for more on learning SQL.

Commonly used queries

You can get started with SQL queries right away using some pre-made examples. Below are 12 examples you can use to get started.

💡 Tip: For more pre-made patterns, browse the SQL Examples page from w3schools—they even have an interactive SQL sandbox where you can practice and learn SQL.

All of the commonly used queries below will use this example table as the database that is being queried.

Fruit SKU Quantity Required Warehouse
Apple 494 400 30 USA
Orange 393 500 10 EU
Tomato 233 200 40 EU
Mango 122 200 80 UK
Lemon 939 200 40 UK
Grape 222     MX

You can follow along with the examples below by downloading and importing the following Workflow demo.

problem downloading? If downloading a Workflow, the file is a unique, Catalytic-specific file type that only works in Catalytic.

If you are unable to download the file, try to right click > save target as...

For more information, check the FAQ question on downloading files from help articles.

After downloading the demo, see How to import a Workflow for how to add it to your team.

Retrieve data from all columns

Query: SELECT * FROM CatalyticTable;

Description: This expression retrieves all data from the database.

Example results:

Fruit SKU Quantity Required Warehouse
Apple 494 400 30 USA
Orange 393 500 10 EU
Tomato 233 200 40 EU
Mango 122 200 80 UK
Lemon 939 200 40 UK
Grape 222     MX
Retrieve data from specific columns

Query: SELECT fruit, warehouse FROM CatalyticTable;

Description: This expression retrieves just the fruit and warehouse columns from the table.

Example results:

Fruit Warehouse
Apple USA
Orange EU
Tomato EU
Mango UK
Lemon UK
Grape MX
Filter data based on a condition

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable WHERE quantity<300

Description: This expression retrieves just the fruit, quantity, and warehouse columns, but only the rows where quantity is less than 300

Example results:

Fruit Quantity Warehouse
Tomato 200 EU
Mango 200 UK
Lemon 200 UK
Filter data based on multiple conditions
Multiple conditions using AND

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable WHERE quantity<300 AND warehouse='UK'

Description: This expression retrieves just the fruit, quantity, and warehouse columns, but only the rows where quantity is less than 300 and the warehouse is in the UK.

Example results:

Fruit Quantity Warehouse
Mango 200 UK
Lemon 200 UK
Multiple conditions using OR

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable WHERE quantity<300 OR warehouse='EU'

Description: This expression retrieves just the fruit, quantity, and warehouse columns, but only the rows where quantity is less than 300 or the warehouse is in the EU.

Example results:

Fruit Quantity Warehouse
Orange 500 EU
Tomato 200 EU
Mango 200 UK
Lemon 200 UK
Filter for distinct rows

Query: SELECT DISTINCT warehouse FROM CatalyticTable

Description: This expression retrieves just the warehouse columns, but only the first unique record in the column. For example, only the first EU and UK rows are returned.

Example results:

Warehouse
USA
EU
UK
MX
Filter columns that are empty

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable WHERE quantity IS NOT NULL

Description: This expression retrieves just the fruit, quantity, and warehouse columns, but only where quantity is not null/empty

Example results:

Fruit Quantity Warehouse
Apple 400 USA
Orange 500 EU
Tomato 200 EU
Mango 200 UK
Lemon 200 UK
Sort data by a specific column
Sort in ascending order

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable ORDER BY fruit

Description: This expression retrieves just the fruit, quantity, and warehouse columns, and orders them alphabetically in ascending order.

Example results:

Fruit Quantity Warehouse
Apple 400 USA
Grape   MX
Lemon 200 UK
Mango 200 UK
Orange 500 EU
Tomato 200 EU
Sort in descending order

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable ORDER BY fruit DESC

Description: This expression retrieves just the fruit, quantity, and warehouse columns, and orders them alphabetically in descending order.

Example results:

Fruit Quantity Warehouse
Tomato 200 EU
Orange 500 EU
Mango 200 UK
Lemon 200 UK
Grape   MX
Apple 400 USA
Filter for values that match a pattern

The LIKE operator in SQL has a unique syntax to help make your query as specific as possible. For example, you can append % or _ to match a character in a specific position, or match for fields that start with a specific character. To learn more about the LIKE operator, check out the SQL LIKE Operator article on w3schools

Query: SELECT fruit, quantity, warehouse FROM CatalyticTable WHERE fruit LIKE '%o%'

Description: This expression retrieves just the fruit, quantity, and warehouse columns, but only where the fruit column contains the letter o in any position. In this case, “Tomato”, “Orange”, “Lemon”, and “Mango” match.

Example results:

Fruit Quantity Warehouse
Orange 500 EU
Tomato 200 EU
Mango 200 UK
Lemon 200 UK
Perform simple calculations on column values

Query: SELECT quantity - required FROM CatalyticTable

Description: This query subtracts the required column from the quantity column and returns a new value in a new column.

Example results:

quantity - required
370
490
160
120
160
Calculate the average of column values

Query: SELECT AVG(quantity) FROM CatalyticTable

Description: This query calculates the average of column and returns a new value in a new column.

Example results:

AVG(quantity)
300
Calculate the sum or average of column values
Average a column

Query: SELECT AVG(quantity) FROM CatalyticTable

Description: This query calculates the average of column and returns a new value in a new column.

Example results:

AVG(quantity)
300
Sum a column

Query: SELECT SUM(quantity) FROM CatalyticTable

Description: This query calculates the sum of column and returns a new value in a new column.

Example results:

SUM(quantity)
1500
Determine the min or max value of a column
Find max from a column

Query: SELECT MAX(quantity) FROM CatalyticTable

Description: This query returns the max value of column and returns it in a new column.

Example results:

MAX(quantity)
500
Find min from a column

Query: SELECT MIN(quantity) FROM CatalyticTable

Description: This query returns the min value of column and returns it in a new column.

Example results:

MIN(quantity)
200

⚠️ Heads-up: At this time, not all SQL functions are supported in Catalytic. For example, JOIN, CONCAT, INNER, DELETE, UPDATE, and INSERT INTO do not currently work. As well, the SQL actions can query one database at a time.

How to configure this action

note
  • Note: Always use the table name CatalyticTable when writing your query. For example SELECT * FROM CatalyticTable. You can choose which source to retrieve data from by changing the Source Table field configuration.

When configuring this action, reference a Table in the Source Table field configuration, and enter a string or reference a field in the SQL Query field configuration. How do I reference a field?

Fields for this action

  • Source Table

    • The Catalytic table to retrieve data from.
  • SQL Query

    • Enter the SQL query to execute. For all queries, use CatalyticTable for the name of the table.
    • For example, SELECT * FROM CatalyticTable
  • Output field prefix

    • To help keep output fields organized, choose an output field prefix to add to the beginning of each output field name as this action may output more than one field.

What will this output?

This action may generate multiple fields. To help keep output fields organized, the prefix above will be added to the beginning of each of the output field names, separated by two dashes. Each field will result as:{{output-field-prefix--output-field}}.

Output fields for this action

  • Result Table

    • The result of the SQL query. The output is a table type field.

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.

My column of integers/decimals is not sorting correctly

This may happen because you have a Short text or Long text type field with integers in it. If you sort a text type field, even if it contains integers, it will sort it as text alphabetically, not numerically.

In the following example, the top right Text column type examples shows how a text field with integers is sorted when sorted in descending or ascending order.

screen readers look here

For information on converting a field to a new field type, see the Convert a field to a new field type article.

The action outputs an empty table or file

The action may return an empty table if a query uses unsupported SQL, or is incorrectly formatted. At this time, not all SQL functions are supported in Catalytic. For example, JOIN, CONCAT, INNER, DELETE, UPDATE, and INSERT INTO do not currently work. As well, the SQL actions can query one database at a time.

If your action is still returning an empty table or file, you can remove and re-add the action.

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.