Patterns & List Sources

BroadSQL recognizes a handful of special macro syntaxes directly inside a SQL query, substituted before the query runs. They fall into two groups: list sources, which expand to a quoted, comma-separated list of values (typically after IN), and execution shortcuts, which run a query instead of typing it out again.

Why use them

Many investigations start with a list you did not type: twenty identifiers in an email or a support request, a column of IDs in a spreadsheet a colleague sent, the IDs a first query just returned. Without help, you paste them into an editor, add quotes and commas, paste the result into IN (...), and do it all again when the list changes. List sources do that step for you, and execution shortcuts spare you retyping a query you just ran.

They help you:

• use a list where it already is: a text file, a CSV or Excel column, the clipboard, or the result on screen, without retyping or reformatting it;
• chain queries: the result of one query feeds the next with <@last:...>;
• repeat a recurring check: the same list file or spreadsheet, the same query, run again tomorrow with the values of the day;
• avoid mistakes: every value is quoted for you, an empty list fails instead of quietly matching nothing, and a spreadsheet column is found by its header, not its position, so a reordered sheet still gives the right values;
• compare Environments: / QA runs the query you just ran here against the QA database, without switching.

Scenarios

SELECT CODE, NAME FROM COUNTRY WHERE CONTINENT = 'South America' AND POPULATION > 20000000;
SELECT COUNTRYCODE, LANGUAGE FROM COUNTRYLANGUAGE WHERE ISOFFICIAL = 'T' AND COUNTRYCODE IN <@last:CODE>;

The second query receives the codes of the first, quoted and separated by commas, and fails clearly if the first returned no rows. These examples run on the WORLD sample; the how-to Investigate a support ticket and keep the evidence shows <@last:...> at work, one step feeding the next.

List sources

Each of these is written as <...> at the point in your SQL where the list belongs, e.g. WHERE COUNTRYCODE IN <@last:CODE>. A source that resolves to zero values fails with a clear error rather than silently producing an empty IN ().

Where a list source starts and ends

A list source starts at <@ and ends at the first > after it, on the same line. Everything else in the statement is ordinary SQL: comparisons such as AMOUNT > 100 before, between or after list sources are never taken as the end of one, so WHERE AMOUNT > 100 AND ID IN <@ids.txt> works as written. A <@ inside a '...' string, a "..." quoted name or a -- or /* */ comment is not a list source. A <@ with no > before the end of its line is reported as an unclosed list source instead of running the statement.

Values read from a list source are inserted as they are: they are never scanned for further list sources.

Relative paths in list sources

A relative path in <@fileName>, <@csv:...> or <@excel:...> is resolved against the working directory of the BroadSQL process (the folder BroadSQL was started from), not against the current Script's folder and not against the Scripts Library. Use an absolute path when the working directory may differ.

Example: started from c:\work, <@csv:data\review.csv:COUNTRY_CODE> reads c:\work\data\review.csv.

<@fileName>

Reads fileName line by line: each line is trimmed, blank lines are skipped. This is the original list-source syntax. The file is read with the same encoding rule as Scripts (see Scripts Library: Encoding).

Example: select count(*) from COUNTRY where CODE in <@c:\codes.txt>

<@csv:<path>:<column>>

Reads one named column from a CSV file, matched by header name (exact match first, then case-insensitive; an ambiguous/duplicate header is rejected). Blank cells in the column are skipped. Parsed with real RFC 4180 parsing (quoted values, embedded separators/quotes). Read as UTF-8; a leading byte-order mark on the header row is stripped automatically.

Example: <@csv:c:\temp\review.csv:COUNTRY_CODE>

<@excel:<path>:<sheet>:<column>>

Reads one named column, on one named sheet, from an .xlsx workbook (sheet and column both matched by name, exact then case-insensitive, never by letter/position, so reordering columns doesn't change which values are used). Numeric cells render as a plain integer or exact decimal (no thousands separators); a date-formatted numeric cell renders as its ISO date/time; a formula cell uses its cached result. The legacy .xls binary format is not supported.

Example: <@excel:c:\temp\review.xlsx:Countries:COUNTRY_CODE>

<@last:<column>>

Reuses one column of the most recently successfully displayed SQL query result: the "rerun a follow-up query against what I just looked at" pattern. Matched by the result's visible column label (so a query alias, e.g. SELECT code AS c, resolves by c), exact match first, then case-insensitive; a label matching more than once is rejected as ambiguous. SQL NULL values in the column are skipped, never rendered as the literal text NULL. Refuses to resolve at all (rather than silently truncating) once the underlying result exceeded the safety row limit.

Example:

SELECT CODE, NAME FROM COUNTRY WHERE CONTINENT = 'South America' AND POPULATION > 20000000;
SELECT COUNTRYCODE, LANGUAGE FROM COUNTRYLANGUAGE WHERE ISOFFICIAL = 'T' AND COUNTRYCODE IN <@last:CODE>;

<@clipboard>

Takes the values currently on the system clipboard, one per line: the "copy one column from Excel/Calc, paste straight into a query" workflow. Read as plain text, split on any line ending; each line trimmed, blank lines skipped. If a copied line contains a tab character (more than one column copied together), this fails clearly rather than guessing which column to use; v1 is single-column only. An empty or non-text clipboard produces an empty list (which then fails the same "zero values" error as any other source).

Example: select * from COUNTRY where CODE in <@clipboard>, after copying codes such as FRA and DEU, one per line. On a fixed-length text column (CHAR), such as NAME in WORLD, compare the trimmed value: where TRIM(NAME) in <@clipboard>.

Execution shortcuts

@<fileName>

Runs a Script: the SQL statements and BroadSQL commands stored in a text file. A plain name such as @myfile.bsql; or @maintenance/myfile.bsql; is a Script in the Scripts Library, @./myfile.bsql; is relative to the working directory (or, inside a Script, to that Script's own folder), and an absolute path such as @c:\dbRestore.bsql; runs a file anywhere. LIB RUN myfile.bsql; runs a library Script through the same pipeline. Both apply Database Group/environment safeguards and take named arguments (@report.bsql year=2026, read in the Script as ${year}). See Running Scripts.

Example: @c:\dbRestore.bsql;

/

Re-runs the last SQL statement, exactly as it was last executed.

/ <environment>

Re-runs the last SQL statement transiently against another environment's connection instead of the current one: handy for re-checking a query you just ran in one environment (e.g. QA) against another (e.g. PROD) without switching connections. The active connection and SHOW QUERY's stored text are left untouched; only that one run targets the other environment.

Example: / QA

Examples

SELECT CODE, NAME FROM COUNTRY WHERE CONTINENT = 'South America' AND POPULATION > 20000000;
SELECT COUNTRYCODE, LANGUAGE FROM COUNTRYLANGUAGE WHERE ISOFFICIAL = 'T' AND COUNTRYCODE IN <@last:CODE>;

CONNECT WORLD;
SELECT COUNT(*) AS CITIES FROM CITY WHERE COUNTRYCODE = 'FRA';
/ QA;

The last line needs the practice connections of Set up QA and production copies of WORLD: it runs the count once on WORLD-QA.

Related pages

• Command reference: every real BroadSQL command; list sources and execution shortcuts are macro syntax, not commands of their own, so they aren't listed there.
• User guide
• Import / Export: what to do with a query's results once you have them.
• Connections, Database Groups and Environments: the Environments that / <environment> runs on.

Found something wrong or unclear on this page? Tell us, with the page address.