Query Syntax

Spacinsider Add-in for Excel Query Syntax

The Spacinsider Add-in for Excel provides a powerful query functionality that allows you to filter, match, and order data within Excel. By constructing queries using the correct syntax, you can retrieve specific and relevant information from your datasets. In this documentation, we will explain the query syntax in a way that is easy to understand and follow.

Basic Components of a Query:

A query consists of different components that work together to define the conditions and operations applied to your data. Here are the basic components of a query:

Selecting the Function: Start by choosing either the DT (Datatable) or DT.COL (DataTable column) function, depending on whether you want to query an entire table or a specific column.
Specifying the Table or Column: Select the table or column you wish to query. If you're querying a custom table, use the keyword 'custom'.
Constructing the Query Condition: The query condition defines the criteria to filter or match data. It comprises one or more expressions and operators.

Expressions:

Expressions form the building blocks of your query condition. They consist of identifiers, constants, and comparison/matching operators. Here are the types of expressions you can use:

Identifiers: Identifiers are used to refer to columns or values within your query. For example, you can use the name of a column or a specific value.
Constants: Constants represent fixed values, such as strings, numbers, or timestamps. They are enclosed in single quotes ('').
Date Literals: Date literals are specific date values represented in the query syntax without using the DATE keyword. They follow the format 'YYYY-MM-DD' and are enclosed in single quotes ('').
Interval Literals: Interval literals represent a duration of time used in queries. They follow the format interval_value unit , where interval_value is a numeric value and unit is one of the supported time units like 'year', 'month', 'day', 'hour', 'minute', or 'second'.
NULL Literal: The null literal represents an absence of value. You can only use the NULL literal with the equals and not equals operators.

Date Keyword Syntax:

The DATE keyword is used in the Spacinsider Add-in for Excel query syntax to specify a date value in a query condition. It is followed by a date value enclosed in single quotes ('') in the format 'YYYY-MM-DD' . The DATE keyword ensures proper identification and handling of the date value within the query. Here's an example of the DATE keyword syntax:

DATE '2022-01-01'

In this example, '2022-01-01' represents the specific date value to be used in the query. Adjust the date value to match your desired date.

Interval Keyword Syntax:

The interval keyword is used in the Spacinsider Add-in for Excel query syntax to specify an interval value in a query condition. It is used in combination with a numeric value and a unit of time, enclosed in single quotes (''), to define the interval duration. The supported units of time include 'year', 'month', 'day', 'hour', 'minute', and 'second' . Here's an example of the interval keyword syntax:

INTERVAL '2 years'

In this example, '2 years' represents an interval of 2 years. Adjust the numeric value and unit of time to match your desired interval duration.

Operators:

Operators define the operations and comparisons performed within your query condition. They allow you to specify how identifiers and constants should be compared or matched. Here are the commonly used operators:

Comparison Operators:

These operators compare values and return a Boolean result.

> (Greater Than): Compares if the left value is greater than the right value.
< (Less Than): Compares if the left value is less than the right value.
= (Equal): Compares if the left value is equal to the right value.
!= (Not Equal): Compares if the left value is not equal to the right value.
>= (Greater Than or Equal): Compares if the left value is greater than or equal to the right value.
<= (Less Than or Equal): Compares if the left value is less than or equal to the right value.

Matching Operators:

These operators are used for pattern matching.
LIKE : The LIKE operator is used to perform string matching or similarity comparisons in your query. It allows you to search for patterns within text data. The LIKE operator is typically used in combination with wildcard characters to match specific patterns. The % symbol represents zero or more characters, and the _ symbol represents a single character.

LIKE Operator Syntax:

The LIKE operator is used in the Spacinsider Add-in for Excel query syntax to perform string matching or similarity comparisons in your query. It allows you to search for patterns within text data. The LIKE operator is typically used in combination with wildcard characters to match specific patterns. Here's the syntax of the LIKE operator:

LIKE 'pattern'

In this syntax, the 'pattern' represents the pattern or substring you want to search for within the text data. The pattern can include wildcard characters to match varying or unknown portions of the text. The % symbol represents zero or more characters, and the _ symbol represents a single character.

Using Wildcard Characters: When using the LIKE operator, you can include wildcard characters in the pattern to broaden or specify the matching criteria. Here are the commonly used wildcard characters:
% (Percentage Symbol): Matches zero or more characters. For example, 'Ap%' will match any string that starts with "Ap".
_ (Underscore): Matches a single character. For example, 'Ba_' will match any string that starts with "Ba" followed by any single character.

Logical Operators:

Logical operators allow you to combine multiple expressions or conditions in your query. Here are the logical operators you can use:

AND : Combines two or more conditions, and all conditions must be true for the overall condition to be true.
OR : Combines two or more conditions, and at least one condition must be true for the overall condition to be true.
NOT : Negates a condition and returns the opposite Boolean result.

Ordering Query Results:

To order your query results, you can include the ORDER BY clause. Specify the column by which you want to order the results, followed by either ASC for ascending order or DESC for descending order.

Example Queries:

Here are a few examples to illustrate how to construct queries using the Spacinsider Add-in for Excel query syntax:

Filtering data based on a specific condition:
- DT("table_name", "column_name > 100")
Matching text values:
- DT("table_name", "column_name LIKE 'Apple%'")
Checking for existence of a column:
- DT("table_name", "column_name != NULL")
Combining multiple conditions using logical operators:
- DT("table_name", "column_name1 > 100 AND column_name2 = 'Value'")
Ordering query results:
- DT("table_name", "ORDER BY column_name ASC")
Querying based on multiple conditions using logical AND :
- DT("table_name", "column1 > 100 AND column2 < 50 AND column3 = 'Value'")
Querying based on multiple conditions using logical OR :
- DT("table_name", "column1 > 100 OR column2 < 50 OR column3 = 'Value'")
Combining AND and OR operators for complex queries:
- DT("table_name", "(column1 > 100 OR column2 < 50) AND column3 = 'Value'")
Nesting multiple conditions with parentheses for precise filtering:
- DT("table_name", "(column1 > 100 AND column2 = 'Value1') OR (column3 < 50 AND column4 = 'Value2')")
Combining logical operators and comparison operators for advanced queries:
- DT("table_name", "column1 > 100 AND (column2 = 'Value1' OR column3 > 50)")
Combining multiple logical operators for complex filtering:
- DT("table_name", "column1 > 100 AND column2 = 'Value1' OR column3 < 50 AND column4 = 'Value2'")
Querying based on a specific date:
- DT("table_name", "date_column = DATE '2022-01-01'")
Querying based on a date range:
- DT("table_name", "date_column >= DATE '2022-01-01' AND date_column <= DATE '2022-12-31'")
Querying based on an interval of years:
- DT("table_name", "date_column >= DATE '2022-01-01' AND date_column < DATE '2022-01-01' + INTERVAL '3 years'")