Back to Insight FastPass

Docs › Your data

FPQL

FPQL (FastPass Query Language) is the language of the Query Workbench. Its statements are shaped like SQL: SELECT … FROM … WHERE … GROUP BY … ORDER BY. Everything inside them, the columns, values and functions, is the same formula language as Calculated Fields and Summary Calculations. If you can write a calculated field, you can write an FPQL WHERE.

22 min read

This guide is the language reference: how to read data, combine it, keep steps in temp tables, write replay queries with and without parameters, and change dataset rows. The page itself (running, saving, sharing) is in Query Workbench.

Plan availability: Plus and Ultimate, for editors and above. Changing dataset rows works on both.

A first example#

-- Open jobs due within @Days days, with each customer's name
DECLARE @Days NUMBER = 7;
DECLARE @Skip TEXT LIST = ["Shipped", "On Hold"];

DROP TABLE IF EXISTS #Open;
SELECT JobID, CustomerID, Status, PromiseDate, Value
INTO #Open
FROM Jobs
WHERE Status NOT IN @Skip
  AND PromiseDate <= DATEADD(TODAY(), @Days, "day");

SELECT o.JobID, c.CustomerName, o.PromiseDate,
       DATEDIFF(TODAY(), o.PromiseDate, "day") AS DaysLeft
FROM #Open AS o
JOIN Customers AS c ON c.CustomerID = o.CustomerID
ORDER BY o.PromiseDate, o.JobID;
  • A one-word column is written as it is: Status, or with the table's alias in front, o.JobID. A name with spaces goes in square brackets: [Promise date], o.[Promise date]. The bracket forms [Status] and o[JobID] (the formula language's spelling) work too, and some examples below use them.
  • Text is in quotes: "Shipped" or 'Shipped'.
  • TODAY(), DATEADD and DATEDIFF are formula functions, so they take their arguments the formula way.
  • #Open is a temp table, kept for this tab (see Temp tables).
  • The script returns one grid: the last SELECT. SELECT … INTO saves its rows instead of showing them.

The rules of the text#

RuleExample
Keywords can be in any case.select, Select and SELECT are the same
End each statement with ;. A line break alone doesn't end one.SELECT 1; SELECT 2;
-- starts a comment to the end of the line; /* … */ can span lines.-- only open jobs
A line with just GO on it splits the script into batches.see Batches
A one-word column needs no brackets. A name with spaces or other characters, or that is a keyword ([Order], [Select]), goes in square brackets. Put the table's alias in front with a dot, or right before the bracket.Qty, j.Qty, [Unit price], j.[Unit price], j[Qty]
A word followed by ( is a function, and a keyword (FROM, AND, NULL…) is never a column.SUM(Qty), TODAY()
A dataset name with spaces goes in brackets or quotes. A dataset in a folder is written Folder.Name.FROM [Order Lines] AS ol, FROM Sales.Orders
Text is in double or single quotes. Double a quote to put one inside."He said ""hi""", 'O''Brien'
Variables start with @, temp tables with #.@Days, #Open
A list is values in square brackets after IN, or as a variable's value.IN ["West", "North"]

Format on the toolbar (or Alt+Shift+F) lays a script out in the SQL Prompt style, without changing what it does; see Formatting.

Reading one dataset#

SELECT [JobID], [Customer], [Qty] * [UnitPrice] AS LineValue
FROM Jobs
WHERE [Status] = "Open" AND [Qty] > 0
ORDER BY [LineValue] DESC, [JobID];
PartWhat to know
SELECTA list of columns or formulas, separated by commas. AS Name names a result column. Without AS, a column keeps its name and a formula is named after its position (Column3 for the third item).
*Every column of every table in FROM. j.* is every column of the table aliased j. In the editor, Tab right after * replaces it with the column list.
DISTINCTSELECT DISTINCT [Region] FROM Jobs drops repeated rows. Text compares ignoring case.
TOP nSELECT TOP 10 … (or TOP (10)) keeps the first n rows after ORDER BY.
FROMOne dataset, temp table or subquery, optionally AS alias. Without an alias, use the dataset's name as the qualifier: Jobs.Qty. For a dataset in a folder, see Folders.
WHEREA formula that is true for the rows to keep. A blank result counts as false.
ORDER BYColumns or formulas, each ASC (the default) or DESC. A result column's name is written in brackets: ORDER BY [LineValue].

Useful WHERE patterns:

WHERE [Status] IN ["Open", "On Hold"]          -- one of several values
WHERE [Qty] BETWEEN 5 AND 10                   -- 5 to 10, both included
WHERE [ShippedOn] IS NULL                      -- blank
WHERE STARTSWITH([JobID], "J-2")               -- instead of LIKE 'J-2%'
WHERE CONTAINS([Notes], "rush")                -- anywhere in the text (ignores case)
WHERE [PromiseDate] >= DATEADD(TODAY(), -30, "day")
WHERE YEAR([Created]) = 2026 AND MONTH([Created]) <= 6

A dataset's calculated fields are columns like any other: SELECT [Margin] FROM Jobs. A calculated field that reads another dataset (Customers[Region]) only works in reports; in a query, join the other dataset instead.

Folders#

Datasets and replay queries can be filed in folders, one level deep. A folder works like a schema in SQL Server: write the folder, a dot, then the name.

SELECT Tasks.Title, Tasks.Status
FROM Ops.Tasks
WHERE Tasks.Status <> "Done";

SELECT a[JobID], o[JobID]
FROM [Archive].[Jobs] AS a
JOIN Ops.Jobs AS o ON o[JobID] = a[JobID];
  • FROM, JOIN, UPDATE, INSERT INTO and DELETE FROM all take Folder.Name. Either part can be in brackets: Sales.[Order Lines], [Ops].[Tasks].
  • Folder and name together are unique, ignoring case, so Sales.Orders and Archive.Orders can both exist.
  • A name without a folder means the top-level dataset of that name if there is one, and otherwise the only dataset of that name in any folder. If several folders have one and none is at the top level, you get error 208: "More than one dataset is named 'Notes' (Archive.Notes, Ops.Notes). Name it with its folder, as Archive.Notes".
  • Columns are qualified by the alias, or by the dataset's name without its folder: Tasks.Title, not Ops.Tasks.Title. Two datasets with the same name from different folders need an alias each.
  • Messages name a dataset with its folder: "(1 row affected) in Ops.Tasks".

Totals and grouping#

SELECT [Region],
       COUNTROWS() AS Jobs,
       SUM([Value]) AS Total,
       AVERAGE([Qty]) AS AvgQty,
       MAX([PromiseDate]) AS Latest
FROM Jobs
WHERE [Status] <> "Shipped"
GROUP BY [Region]
HAVING SUM([Value]) > 1000
ORDER BY [Total] DESC;
  • Every column in the SELECT must be in the GROUP BY or inside a total (error 8120 says which one isn't).
  • Totals are the formula aggregates: SUM, AVERAGE, MIN, MAX, COUNT (non-blank values), COUNTROWS() (rows; COUNT(*) means the same), DISTINCTCOUNT, MEDIAN and the rest of the aggregate functions.
  • WHERE filters rows before grouping and can't use a total (error 147). HAVING filters the groups after.
  • A total with no GROUP BY gives one row: SELECT SUM([Value]) AS Total FROM Jobs.
  • Text groups ignore case, and all blanks form one group.
  • A dataset's Summary Calculations can be used by name, like a field: [Gross margin %], or j[Gross margin %] with the table's alias. FPQL writes the calculation's formula in where the name is, so it's a total: use it in the SELECT list, HAVING or ORDER BY of a grouped query, or alone for a whole-table total, but not in WHERE or GROUP BY (error 147). Selected without AS, the column takes the calculation's name.
  • A column or calculated field with the same name wins over a Summary Calculation. If two tables in the query both have a Summary Calculation of that name, add the alias (error 209).
  • A Summary Calculation that reads a connected dataset (Customers[Region] in its formula) needs that dataset joined in the query, or you get error 4104: "[West value] reads Customers, so join Customers to use it here". One that names another Summary Calculation works too; two that name each other in a circle give error 50006.
  • You see a dataset's Summary Calculations in the Object Explorer and the editor offers them as you type in the SELECT list, HAVING and ORDER BY. The text you saved keeps the name as you wrote it, so a change to the calculation shows the next time the query runs.
SELECT j[Status], [Total value], [Gross margin %]
FROM Jobs AS j
JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
GROUP BY j[Status]
ORDER BY [Gross margin %] DESC;

Rankings and running totals work over the groups of a grouped query:

SELECT [Region], SUM([Value]) AS Total,
       RANK(SUM([Value])) AS Place,
       PERCENTOFTOTAL(SUM([Value])) AS Share,
       RUNNINGTOTAL(SUM([Value]), [Region]) AS Running
FROM Jobs
GROUP BY [Region];

RANK, RUNNINGTOTAL, PREVIOUS and PERCENTOFTOTAL need a GROUP BY; a query without one gives error 147.

Joining tables#

SELECT j[JobID], c[CustomerName], o[Operation]
FROM Jobs AS j
JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
LEFT JOIN Operations AS o ON o[JobID] = j[JobID] AND o[Status] = "Running";
JoinKeeps
JOIN (or INNER JOIN)Rows that match on both sides
LEFT JOINEvery row of the left side; the right side's columns are blank where nothing matched
FULL JOINEvery row of both sides
  • Every join needs an ON. It can test more than one pair of columns, and extra conditions, joined with AND.
  • A join can read a dataset, a temp table, or a ( SELECT … ) in brackets with an alias.
  • A blank key never matches anything, not even another blank.
  • Text keys match ignoring case, and numbers match numbers (7 matches 7.0).
  • When two joined tables have a column of the same name, write it with its alias. [CustomerID] alone gives error 209, "in more than one table".
  • Up to 8 joins in one SELECT. RIGHT JOIN isn't supported: swap the tables and use LEFT JOIN.

Subqueries#

A SELECT in brackets can stand in for a table in FROM or a join. Give it an alias, and name its formula columns with AS:

SELECT c[CustomerName], t[Total]
FROM Customers AS c
JOIN (SELECT [CustomerID], SUM([Value]) AS Total
      FROM Jobs
      GROUP BY [CustomerID]) AS t
  ON t[CustomerID] = c[CustomerID]
WHERE t[Total] > 500
ORDER BY t[Total] DESC;

Subqueries can nest 3 deep. IN (SELECT …) isn't supported: join to the subquery, or load it into a temp table first.

Combining results#

SELECT [Region] FROM Jobs
UNION
SELECT [Region] FROM Customers
ORDER BY [Region];
OperatorResult
UNIONRows from either side, repeats removed
UNION ALLEvery row from both sides
EXCEPTRows of the first side that aren't in the second

Each side needs the same number of columns; the names come from the first SELECT. One ORDER BY at the end sorts the combined result. INTERSECT isn't supported.

Blanks (NULL)#

A blank is FPQL's NULL. A value is blank when it is missing or "" (empty text). The grid shows blanks as NULL.

  • 0, FALSE, "0" and " " are not blank.
  • IFNULL([Region], "Unknown") gives "Unknown" for a blank region, including "". COALESCE(a, b, c) gives the first that isn't blank.
  • [A] = [B] is true when both are blank. <> between a blank and a value is true.
  • Comparing a blank with <, <=, > or >= is false. AND and OR treat a blank as false, and NOT of a blank is true.
  • Dividing by zero gives a blank, not an error.
  • Text comparisons ignore case: "west" = "West".
  • In a join, keys with a blank part never match. GROUP BY puts all blanks in one group.

Variables and lists#

DECLARE @Since DATE = DATE(2026, 1, 1);
DECLARE @Regions TEXT LIST = ["West", "North"];
SET @Since = DATEADD(TODAY(), -30, "day");

SELECT * FROM Jobs
WHERE [Created] >= @Since
  AND (@Regions IS NULL OR [Region] IN @Regions);
  • Types: TEXT, NUMBER, DATE, BOOLEAN, and a list of any of them: TEXT LIST, NUMBER LIST, DATE LIST, BOOLEAN LIST.
  • DECLARE without = value starts the variable blank (an empty list for a list). Several can be declared at once: DECLARE @A NUMBER = 1, @B TEXT = "x";.
  • A value is converted to the variable's type when it's set, and one that can't be gives error 50006 ("'abc' isn't a number (the type is NUMBER)").
  • A list is written in square brackets: ["West", "North"], [1, 2, 3] or []. It can hold up to 1,000 values, which are typed values or single-value variables. Blank values are left out of a list.
  • A list is only used with IN / NOT IN, and with IS NULL / ISBLANK. A missing or empty list is blank, so (@Regions IS NULL OR [Region] IN @Regions) makes the filter optional.
  • [ starts a list right after IN / NOT IN and as the value in DECLARE, SET and EXEC; everywhere else it starts a column.

Batches#

A line with just GO on it splits a script into batches:

  • Variables last for their batch only; declare them again after a GO.
  • An error stops the rest of its batch, and the next batch still runs.
  • Temp tables last across batches (they belong to the tab).

Temp tables#

A temp table is a table you make in a query tab, named with #. It belongs to that tab: another tab, or another person, can have their own #Open. It is a copy of the rows when it was made; later changes to the dataset don't reach it. Use temp tables to build a result in steps, the way you'd use a WITH or a scratch table in SQL.

StatementEffect
SELECT … INTO #Name FROM …Makes it. Fails (error 2714) if #Name exists: use DROP TABLE IF EXISTS #Name first.
INSERT INTO #Name EXEC [Replay] …Adds the replay's last result. Makes #Name if it doesn't exist.
INSERT INTO #Name SELECT … / VALUES …Adds rows to an existing temp table, matching columns by position or by the list given.
UPDATE #Name SET … [WHERE …], DELETE FROM #Name [WHERE …]Changes or removes rows. No WHERE means every row.
UPDATE t SET … FROM #Name AS t JOIN …, DELETE t FROM #Name AS t JOIN …Changes or removes rows using a join (see Changing rows with a join).
DROP TABLE [IF EXISTS] #NameRemoves it.
DROP TABLE IF EXISTS #Late;
SELECT [JobID], [CustomerID], [PromiseDate] INTO #Late
FROM Jobs WHERE [PromiseDate] < TODAY() AND [Status] <> "Shipped";

INSERT INTO #Late ([JobID], [PromiseDate]) VALUES ("J-TEST", TODAY());
DELETE FROM #Late WHERE [CustomerID] IS NULL;

SELECT COUNTROWS() AS LateJobs FROM #Late;
  • Temp tables are listed under Temporary Tables in the Object Explorer, and you can right-click one for Select Top 1000 Rows or Drop.
  • They are kept while the tab is open, and across a page reload. They're dropped when you close the tab, or about 20 minutes after you leave the page (2 minutes after closing the browser tab).
  • A temp table remembers whose rows it holds: if your access to a dataset it came from changes, it is dropped, with a message.
  • One temp table holds up to 2,000,000 rows.

Replay queries#

A replay query is a script saved under a name that anyone in the workspace who can use the Workbench can run with EXEC. It's the Workbench's stored procedure, and every one is shared with the workspace. It can have parameters (values the caller passes in) or none at all. The Query Workbench guide shows the buttons for saving and changing them; this section is the syntax.

Without parameters#

The simplest replay is a saved SELECT:

CREATE REPLAY [Open jobs by customer] AS
SELECT c[CustomerName], COUNTROWS() AS OpenJobs, SUM(j[Value]) AS OpenValue
FROM Jobs AS j
JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
WHERE j[Status] <> "Shipped"
GROUP BY c[CustomerName]
ORDER BY [OpenValue] DESC;

Run that once (F5) to save it; Messages says "Replay query [Open jobs by customer] saved." From then on, any script can run it:

EXEC [Open jobs by customer];

With parameters#

Parameters go between the name and AS, separated by commas, each as @Name TYPE with an optional = default. A replay with several statements wraps them in BEGIN … END:

CREATE OR ALTER REPLAY [Late jobs]
    @Days NUMBER = 7,
    @Since DATE = DATEADD(TODAY(), -90, "day"),
    @Customers TEXT LIST = [],
    @Region TEXT
AS
BEGIN
    SELECT j[JobID], c[CustomerName], c[Region], j[PromiseDate]
    INTO #Late
    FROM Jobs AS j
    JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
    WHERE j[Status] <> "Shipped"
      AND j[Created] >= @Since
      AND j[PromiseDate] <= DATEADD(TODAY(), @Days, "day")
      AND (@Customers IS NULL OR c[CustomerName] IN @Customers)
      AND (@Region IS NULL OR c[Region] = @Region);

    SELECT * FROM #Late ORDER BY [PromiseDate];
END;
  • Types are the same as for variables: TEXT, NUMBER, DATE, BOOLEAN, and … LIST for a list.
  • Defaults are formulas worked out each time the replay runs, so DATEADD(TODAY(), -90, "day") always means "90 days ago".
  • A parameter without a default (like @Region above) is blank when the caller leaves it out. Write the filter as (@Region IS NULL OR …) to make it optional, as above.
  • List parameters are the way to pass "any of these values": test them with IN, and use (@List IS NULL OR …) so an empty list means "all".
  • Brackets around the parameter list are allowed: CREATE REPLAY [X] (@Days NUMBER = 7) AS ….

Calling a replay#

EXEC [Late jobs];                                         -- every parameter at its default
EXEC [Late jobs] @Days = 14;                              -- named: the rest keep their defaults
EXEC [Late jobs] @Days = 30, @Customers = ["Northline Aero", "Kestrel Defense"];
EXEC [Late jobs] 14;                                      -- by position, in the header's order

DECLARE @Mine TEXT LIST = ["Northline Aero"];
EXEC [Late jobs] @Customers = @Mine;                      -- a variable as the value

INSERT INTO #Late EXEC [Late jobs] @Days = 30;            -- keep its last result in a temp table
  • EXECUTE works too.
  • Each SELECT the replay returns shows as its own grid. INSERT INTO #Name EXEC … keeps only the last result, and creates #Name if it doesn't exist.
  • A value is converted to the parameter's type. A name the replay doesn't have gives "[Late jobs] has no parameter @Dayz", and too many values by position gives "[Late jobs] takes 4 arguments".
  • An error inside the replay names it: "In replay query [Late jobs]: …".

Names and sharing#

Every replay query is shared with the workspace: everyone who can use the Workbench sees it and can run it with EXEC [Name]. There are no private replays. Names can't contain [ or ].

A replay can be filed in a folder, the same way as a dataset: write the folder before the name.

CREATE OR ALTER REPLAY TaskQueries.AllTasks_Get AS
SELECT * FROM Ops.Tasks;
GO
EXEC TaskQueries.AllTasks_Get;
DROP REPLAY IF EXISTS TaskQueries.[All tasks];
  • Folder and name together are unique, ignoring case, so two folders can each have a [Same].
  • EXEC and DROP REPLAY with a folder use the replay in that folder. Without one, they use the top-level replay of that name, or else the only one of that name in any folder. If several folders have one, the error says "More than one replay query is named [Same] (A.[Same], B.[Same]). Name it with its folder".
  • CREATE REPLAY and CREATE OR ALTER REPLAY save to exactly the folder written; no folder means the top level.
  • my and shared are ordinary folder names: EXEC shared.[X] looks in a folder named shared.
  • Messages use the folder too: "Replay query TaskQueries.[OpenTasks] saved."

The person who created a replay is its owner. Only the owner, the workspace owner and admins can change or delete it.

Saving, changing and deleting#

StatementEffect
CREATE REPLAY [Name] …Saves a new replay, shared with the workspace. Fails if there is already one of that name in that folder.
CREATE OR ALTER REPLAY [Name] …Saves it, or replaces the one of that name in that folder if you're its owner, the workspace owner or an admin.
DROP REPLAY [IF EXISTS] [Name]Deletes it (its owner, the workspace owner or an admin).
  • The script is checked when it's saved: an unknown dataset or column, or a syntax error, stops the save and nothing is stored.
  • Where the replay ends: with BEGIN … END, at END. Without them, the replay is every statement up to the end of the batch. So either use BEGIN … END, or put GO on the line after a CREATE REPLAY if more of the script follows.
  • A replay can't create another replay, and a DECLARE in its body can't reuse a parameter's name.

What a replay can do#

  • Whose data: a replay always runs with the access of the person who runs it: their datasets, their row access and their right to change rows. Sharing one never shares data, or the right to change data, that the other person doesn't already have.
  • Temp tables: a replay can read the caller's temp tables. Temp tables it makes are dropped when it ends, so return what the caller needs with a SELECT.
  • Nesting: replays can call other replays, up to 4 deep. A replay that calls itself, directly or through others, is stopped at that depth.
  • Behind a dataset: on Ultimate a replay can be saved as a live dataset that reports feed with values (Saving a replay as a live dataset). Its LIST parameters take a slicer's several values, and a DATE parameter a relative date such as startOfMonth. A replay used by a dataset's write action runs once per saved row, with the rights of the action's author.

Changing datasets#

INSERT, UPDATE and DELETE work on datasets as well as temp tables when all of these hold:

  • the workspace is on Plus or Ultimate;
  • you are the workspace owner or an admin, or an admin made you a query editor on that dataset;
  • you have edit access to the dataset (you uploaded it, you're the owner or an admin, or you can edit a report that uses it).

Setting up who can change datasets walks through it. Datasets you can change have a pencil in the Object Explorer.

-- Change some rows
UPDATE Jobs SET [Status] = "On Hold", [Notes] = "Credit check" WHERE [CustomerID] = 4;

-- Add rows: name the columns, give one row per bracket
INSERT INTO Jobs ([JobID], [CustomerID], [Status], [Qty])
VALUES ("J-3001", 4, "Open", 12),
       ("J-3002", 4, "Open", 3);

-- Add rows from a query
INSERT INTO Jobs ([JobID], [CustomerID], [Status])
SELECT n[JobID], n[CustomerID], "Open" FROM #NewJobs AS n;

-- Remove rows
DELETE FROM Jobs WHERE [Status] = "Cancelled" AND [Created] < DATE(2025, 1, 1);
  • UPDATE and DELETE on a dataset need a WHERE. Write WHERE TRUE to change every row.
  • One statement changes at most 10,000 rows of a dataset.
  • Each statement is saved as soon as it runs, as one change in the dataset's history. Messages says "(n rows affected) in dataset, commit …". Rows that couldn't be changed are listed with the reason, for example "(2 rows not changed: outside the rows you can access)", and the others are still saved.
  • You only change rows you can see, and you can't add a row you wouldn't be able to see.
  • The dataset's own rules still apply: column types, key columns on input datasets, locked older snapshots and read-only calculated fields. Statements can't add, rename or remove columns.
  • A dataset's key fields (set in its write-back) are read-only to everyone but the workspace owner and admins: an UPDATE that sets one stops with 50003 "OrderId is a key field", even when the value doesn't change. INSERT may give them values, and DELETE works as usual.
  • A query dataset's rows come from its query, so it can't be changed this way.
  • The first time a tab runs a script that changes datasets, the Workbench asks you to confirm.

Changing rows with a join#

-- Copy each job's current status into the temp table
UPDATE t
SET [Status] = j[Status], [PromiseDate] = j[PromiseDate]
FROM #Open AS t
JOIN Jobs AS j ON j[JobID] = t[JobID]
WHERE j[Status] <> t[Status];

-- Remove jobs that have no running operation
DELETE t
FROM #Open AS t
LEFT JOIN Operations AS o ON o[JobID] = t[JobID] AND o[Status] = "Running"
WHERE o[JobID] IS NULL;
  • The table named after UPDATE or DELETE (an alias from the FROM, or the table itself) is the only one that changes. The others are only read, with your row access.
  • JOIN and LEFT JOIN are allowed; FULL JOIN isn't, because it could add rows that aren't in the target.
  • If a row would get a value from more than one joined row, the UPDATE stops: "… matches n rows in the join, so the new value is ambiguous". Narrow the join, or total the other side in a subquery first.
  • The same works on datasets, with the same rights and the WHERE rule as above.

Common tasks#

Find duplicates:

SELECT [JobID], COUNTROWS() AS Copies
FROM Jobs
GROUP BY [JobID]
HAVING COUNTROWS() > 1;

Rows in one dataset with no match in another:

SELECT j[JobID]
FROM Jobs AS j
LEFT JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
WHERE c[CustomerID] IS NULL;

Top 5 customers this year:

SELECT TOP 5 c[CustomerName], SUM(j[Value]) AS Total
FROM Jobs AS j
JOIN Customers AS c ON c[CustomerID] = j[CustomerID]
WHERE YEAR(j[Created]) = YEAR(TODAY())
GROUP BY c[CustomerName]
ORDER BY [Total] DESC;

Build a result in steps:

DROP TABLE IF EXISTS #Totals;
SELECT [CustomerID], SUM([Value]) AS Total INTO #Totals FROM Jobs GROUP BY [CustomerID];

SELECT c[CustomerName], t[Total], IF(t[Total] > 1000, "Key", "Standard") AS Tier
FROM #Totals AS t
JOIN Customers AS c ON c[CustomerID] = t[CustomerID]
ORDER BY t[Total] DESC;

Bucket values:

SELECT CASE([Qty] >= 100, "Large", [Qty] >= 10, "Medium", "Small") AS Size, COUNTROWS() AS Jobs
FROM Jobs
GROUP BY CASE([Qty] >= 100, "Large", [Qty] >= 10, "Medium", "Small");

Coming from T-SQL#

FPQL looks like T-SQL, but its expressions are formulas. These T-SQL spellings are refused, and the error says what to write instead:

T-SQLFPQL
CASE WHEN … THEN … ENDIF(test, then, else) or CASE(test1, value1, …, default)
x LIKE 'ab%'STARTSWITH(x, "ab"), CONTAINS, ENDSWITH
CAST(x AS …)VALUE(x), TEXT(x), DATEVALUE(x)
GETDATE()NOW() or TODAY()
ISNULL(a, b)IFNULL(a, b)
DATEDIFF(day, a, b)DATEDIFF(a, b, "day")
a % bMOD(a, b)
COUNT(DISTINCT x)DISTINCTCOUNT(x)
VARCHAR, INT, DATETIME, …TEXT, NUMBER, DATE, BOOLEAN
RIGHT JOINswap the tables and use LEFT JOIN
WITH cte AS (…)a temp table: SELECT … INTO #Step
x IN (SELECT …)a join, or a temp table and a join
CREATE PROCEDURECREATE OR ALTER REPLAY
CROSS JOIN, FROM a, b, INTERSECTnot supported
CREATE TABLE, TRUNCATE, MERGE, USE, SET NOCOUNTnot supported; temp tables come from SELECT … INTO

Other differences:

  • '' (empty text) is NULL.
  • NULL = NULL is true, and <> between NULL and a value is true.
  • Dividing by zero gives NULL.
  • Text comparisons ignore case.
  • TODAY() and NOW() use your browser's time zone.

Errors#

Errors show in Messages as Msg code, Line n: message. Click one to jump to it. The editor underlines most of them as you type, before you run.

CodeMeaning
102Syntax: something is missing or out of place
156A keyword where it can't go
207Unknown column, or a variable that isn't declared
208Unknown dataset, temp table or replay query, or a name that more than one folder has (add the folder)
209Ambiguous column: more than one table in the query has it; add the alias
4104An alias that isn't in the query, or a Summary Calculation that reads a dataset the query doesn't join (join it)
8120A column in the SELECT that isn't in the GROUP BY or inside an aggregate
147An aggregate where it isn't allowed, such as in WHERE, or a ranking without GROUP BY
2714The temp table already exists
50001Not supported in FPQL
50002A T-SQL spelling; the message gives the FPQL form
50003Not allowed, for example changing a dataset you can't change, UPDATE without WHERE, setting a dataset's key field, or a temp table dropped because your access changed
50004Over a limit (rows, time, list size, temp table size)
50005A dataset changed while the statement ran; run it again
50006Wrong type, an unknown function, the wrong number of arguments, or Summary Calculations that name each other in a circle
50007An error while running, such as a bad value
50008Cancelled

Limits#

LimitValue
Time per run30 seconds
Rows returned per result10,000 in the Workbench (the server allows up to 100,000)
Result sets per run50
Joins per SELECT8
Subqueries inside subqueries3 deep
EXEC inside EXEC4 deep
Values in a list1,000
Rows changed per dataset statement10,000
Rows in a temp table2,000,000
Scripts running at once1 per tab, 3 per person