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.
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]ando[JobID](the formula language's spelling) work too, and some examples below use them. - Text is in quotes:
"Shipped"or'Shipped'. TODAY(),DATEADDandDATEDIFFare formula functions, so they take their arguments the formula way.#Openis a temp table, kept for this tab (see Temp tables).- The script returns one grid: the last
SELECT.SELECT … INTOsaves its rows instead of showing them.
The rules of the text#
| Rule | Example |
|---|---|
| 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];
| Part | What to know |
|---|---|
SELECT | A 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. |
DISTINCT | SELECT DISTINCT [Region] FROM Jobs drops repeated rows. Text compares ignoring case. |
TOP n | SELECT TOP 10 … (or TOP (10)) keeps the first n rows after ORDER BY. |
FROM | One 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. |
WHERE | A formula that is true for the rows to keep. A blank result counts as false. |
ORDER BY | Columns 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 INTOandDELETE FROMall takeFolder.Name. Either part can be in brackets:Sales.[Order Lines],[Ops].[Tasks].- Folder and name together are unique, ignoring case, so
Sales.OrdersandArchive.Orderscan 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, notOps.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
SELECTmust be in theGROUP BYor 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,MEDIANand the rest of the aggregate functions. WHEREfilters rows before grouping and can't use a total (error 147).HAVINGfilters the groups after.- A total with no
GROUP BYgives 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 %], orj[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 theSELECTlist,HAVINGorORDER BYof a grouped query, or alone for a whole-table total, but not inWHEREorGROUP BY(error 147). Selected withoutAS, 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
SELECTlist,HAVINGandORDER 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";
| Join | Keeps |
|---|---|
JOIN (or INNER JOIN) | Rows that match on both sides |
LEFT JOIN | Every row of the left side; the right side's columns are blank where nothing matched |
FULL JOIN | Every row of both sides |
- Every join needs an
ON. It can test more than one pair of columns, and extra conditions, joined withAND. - 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 (
7matches7.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 JOINisn't supported: swap the tables and useLEFT 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];
| Operator | Result |
|---|---|
UNION | Rows from either side, repeats removed |
UNION ALL | Every row from both sides |
EXCEPT | Rows 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.ANDandORtreat a blank as false, andNOTof 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 BYputs 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. DECLAREwithout= valuestarts 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 withIS 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 afterIN/NOT INand as the value inDECLARE,SETandEXEC; 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.
| Statement | Effect |
|---|---|
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] #Name | Removes 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… LISTfor 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
@Regionabove) 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
EXECUTEworks too.- Each
SELECTthe replay returns shows as its own grid.INSERT INTO #Name EXEC …keeps only the last result, and creates#Nameif 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]. EXECandDROP REPLAYwith 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 REPLAYandCREATE OR ALTER REPLAYsave to exactly the folder written; no folder means the top level.myandsharedare 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#
| Statement | Effect |
|---|---|
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, atEND. Without them, the replay is every statement up to the end of the batch. So either useBEGIN … END, or putGOon the line after aCREATE REPLAYif more of the script follows. - A replay can't create another replay, and a
DECLAREin 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
LISTparameters take a slicer's several values, and aDATEparameter a relative date such asstartOfMonth. 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);
UPDATEandDELETEon a dataset need aWHERE. WriteWHERE TRUEto 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
UPDATEthat sets one stops with 50003 "OrderId is a key field", even when the value doesn't change.INSERTmay give them values, andDELETEworks 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
UPDATEorDELETE(an alias from theFROM, or the table itself) is the only one that changes. The others are only read, with your row access. JOINandLEFT JOINare allowed;FULL JOINisn'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
UPDATEstops: "… 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
WHERErule 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-SQL | FPQL |
|---|---|
CASE WHEN … THEN … END | IF(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 % b | MOD(a, b) |
COUNT(DISTINCT x) | DISTINCTCOUNT(x) |
VARCHAR, INT, DATETIME, … | TEXT, NUMBER, DATE, BOOLEAN |
RIGHT JOIN | swap 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 PROCEDURE | CREATE OR ALTER REPLAY |
CROSS JOIN, FROM a, b, INTERSECT | not supported |
CREATE TABLE, TRUNCATE, MERGE, USE, SET NOCOUNT | not supported; temp tables come from SELECT … INTO |
Other differences:
''(empty text) is NULL.NULL = NULLis true, and<>between NULL and a value is true.- Dividing by zero gives NULL.
- Text comparisons ignore case.
TODAY()andNOW()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.
| Code | Meaning |
|---|---|
| 102 | Syntax: something is missing or out of place |
| 156 | A keyword where it can't go |
| 207 | Unknown column, or a variable that isn't declared |
| 208 | Unknown dataset, temp table or replay query, or a name that more than one folder has (add the folder) |
| 209 | Ambiguous column: more than one table in the query has it; add the alias |
| 4104 | An alias that isn't in the query, or a Summary Calculation that reads a dataset the query doesn't join (join it) |
| 8120 | A column in the SELECT that isn't in the GROUP BY or inside an aggregate |
| 147 | An aggregate where it isn't allowed, such as in WHERE, or a ranking without GROUP BY |
| 2714 | The temp table already exists |
| 50001 | Not supported in FPQL |
| 50002 | A T-SQL spelling; the message gives the FPQL form |
| 50003 | Not 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 |
| 50004 | Over a limit (rows, time, list size, temp table size) |
| 50005 | A dataset changed while the statement ran; run it again |
| 50006 | Wrong type, an unknown function, the wrong number of arguments, or Summary Calculations that name each other in a circle |
| 50007 | An error while running, such as a bad value |
| 50008 | Cancelled |
Limits#
| Limit | Value |
|---|---|
| Time per run | 30 seconds |
| Rows returned per result | 10,000 in the Workbench (the server allows up to 100,000) |
| Result sets per run | 50 |
Joins per SELECT | 8 |
| Subqueries inside subqueries | 3 deep |
EXEC inside EXEC | 4 deep |
| Values in a list | 1,000 |
| Rows changed per dataset statement | 10,000 |
| Rows in a temp table | 2,000,000 |
| Scripts running at once | 1 per tab, 3 per person |