Queries

You'll be presented the SQL queries syntax, as well as examples using SQL on SlicingDice.


SQL Syntax

Below is the SELECT supported syntax.

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  {
    *
    | {
        <expression> [ [ AS ] <column_reference> ]
        | { <table_name> | <correlation_name> } .*
      } [ , ... ]
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  {
    FROM <table_reference> [ [ AS ] <identifier> ]
  } [ , ... ]
  [ [
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
  ] [ ... ]
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [
    ORDER BY
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [
      { OFFSET | , }
      <expression>
    ]
  ]
}

Select Limits

Below is the list of limits and restrictions for querying using SQL.

Operation Limits
Column Name Reference For reference a single name column, just insert in the parameters of the dimension. If a column has a hyphen or is a broken event column, you need to insert brackets around them. Example: [entity-id], [eventcol.0.value].
Supported Conditions The supported search conditions are the following:
= | > | < | >= | <= | <> | != | LIKE | NOT_LIKE | IN | NOT_IN | IS_NULL | IS_NOT_NULL | AND | OR | CONTAINS | BETWEEN | AND | OR
Boolean Values Must be True or False
String Values Values for unique-id, string and string-event columns can have at most 1,000 characters and are only stored in lowercase and need to be inside quotation marks.
Integer Values Values for integer and integer-event columns must be in the interval from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Decimal Values Values for decimal and decimal-event columns must be in the interval from (-263/10p) to ((263 - 1)/10p) given p decimal place and cannot have more than 20 decimal places.
Decimal separators are done with dots. Ex: 90.923
Date Values Values for date column must be in YYYY-MM-DD format and need to be inside quotation marks.
Datetime Values Values for datetime column must be in YYYY-MM-DDTHH:MM:SSZ format and need to be inside quotation marks.
Event Values Dates in string-event, integer-event, decimal-event and geolocation-event cannot be older than 1990-01-01.
Eventual Consistency The selected values will be ready to be queried up to 5 seconds after insertion.

Query examples

Return all columns.
SELECT * FROM account;

Select a column with a custom name
SELECT [companyname] AS MY_companyname FROM account;

Cast a column's data as a different data type
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue
        FROM account;

Search data
SELECT * FROM account
        WHERE industry = 'retail';

Return the number of items matching the query criteria
SELECT COUNT(*) AS MyCount
        FROM account;

Return the number of unique items matching the query criteria
SELECT COUNT(DISTINCT companyname) FROM account;

Summarize data
SELECT companyname, MAX(AnnualRevenue)
        FROM account
        GROUP BY companyname;

Retrieve data from multiple tables.
SELECT [Customer.FirstName], [Customer.LastName], [Purchases.ItemName]
        FROM Purchases
        INNER JOIN Customer ON [Purchases.CustomerId] = Customer.Id;

Sort a result set in ascending order
SELECT [entity-id], companyname
        FROM account
        ORDER BY companyname ASC;

Restrict a result set to the specified number of rows
SELECT [entity-id], companyname FROM account LIMIT 10;