SELECT syntax

SELECT syntax is very intuitive, see the examples.

Basic SELECT #

If you need just the data as is, get them using the usual syntax:

SELECT    *
FROM      Aero.DIM_PLANES -- DataSourceName.SheetName
ORDER BY  PLANE_ID

Don’t forget sets match expectation requires ordered sets, include ORDER BY if you use that.

You can use all common stuff you are used to, such as WHERE, GROUP BY, ORDER BY, HAVING, WITH (for common table expressions) etc. You can use expressions, logical operators, …

Case Sensitivity, Naming #

Names of tables, columns, functions are case insensitive, keywords like SELECT, FROM are also case insensitivie. But beware in the Sheets: setting in data source, names of sheets are case sensitive (the ony exception).

Both name of a data source and name of an MS Excel sheet can contain spaces and non-Unicode characters. This is not a problem, but you need to enclose the names in double-quotes:

SELECT    ID, FirstName, LastName, " Some weird column Name 123"
FROM      "Aero data"."Person Incremental Load"

In the example above, your data source Aero data has space in name. The same for the sheet (Person Incremental Load) and column name in the sheet (the last one).

Limit Number of Returned Rows #

You can use LIMIT (and even OFFSET if needed) to retrieve only part of the result set:

SELECT    FLIGHT_NUMBER
FROM      Aero.FACT_DEPARTURES
WHERE     Passengers <= 10
LIMIT     1

(Similar as SELECT TOP in MS SQL server.) This is useful for optimizing the queries.

Tip #

FROM clause is optional. You can leverage this for easily declaring what data you expect to get:

Test:
- Name:           Overbooked flights
  Description:    We want to ingnore 5 known problems that occured, but fail if others will occur
  First data source: Aero
  First query:    SELECT COUNT(*), MAX(YEAR(DATE_OF_FLIGHT)) FROM FACT.DEPARTURES WHERE Passengers > 200
  Second data source: Aero
  Second query:   SELECT 5, 2014
  Expectation:    sets match