SQL Directives

Directives let you control how a result set looks — the tab title, which columns show, the order they appear in, and how numbers and dates are formatted — by adding a few special comment lines to the top of your SQL. They live in your query, so the presentation travels with it: re-run the query, copy it into a new tab, or save it to your Library, and the formatting comes along.

Oracle never sees them. Each directive line is stripped to a plain -- comment before your SQL is sent to Fusion, so they have no effect on the data that comes back — only on how Data Collage displays it.

Writing a directive

A directive is a comment that starts with --@, followed by a name, a separator, and a value:

--@title: Top AP Invoices

A few things the parser is relaxed about:

• The separator can be a colon or an equals sign — --@title: Foo and --@title = Foo both work.
• Names are case-insensitive — --@TITLE is fine.
• Extra spaces around the @, the name, and the separator are tolerated.
• You can wrap the value in single or double quotes; the quotes are removed.

Directives go at the very top

Data Collage reads directives only from the leading comment block of your SQL. It scans from the first line, accepting blank lines, ordinary -- comments, and --@ directives, and stops the moment it hits real SQL. Anything below your first line of SQL is ignored, so always keep your directives above the SELECT.

--@title: Open Invoices
--@hide: ORG_ID
-- this ordinary comment is fine here too

SELECT invoice_num, org_id, invoice_amount   -- directives below here are NOT read
FROM   ap_invoices_all

If you use the same directive twice, the first one wins and the rest are ignored.

When you run a file with several statements, each statement’s directives apply only to that statement — they don’t leak onto the others.

Naming columns

--@hide, --@show, --@column_order, and --@format all refer to columns by name. Use the column’s heading as it appears in the grid — that is, your AS alias if you gave one. Matching is case-insensitive, and any name that doesn’t match a real column is simply skipped (a typo never blanks your grid). After each run, a small toast tells you what was applied and what was skipped.

The directives

Directive	What it does
--@title	Renames the tab
--@hide	Hides the listed columns
--@show	Hides everything except the listed columns
--@column_order	Reorders the columns
--@format	Formats numbers and dates per column

--@title — name the tab

--@title: Top 10 AP Invoices

The tab is renamed as you type — you don’t have to run the query. The title is sticky: if you later delete the directive, the tab keeps the last name it was given rather than reverting.

--@hide — hide columns

--@hide: ORG_ID, CREATED_BY, LAST_UPDATE_DATE

Hides each listed column from the grid. The data is still there — you can unhide from the Columns menu — it just starts out hidden. If none of the names match, nothing happens.

--@show — keep only these columns

--@show: INVOICE_NUM, VENDOR_NAME, INVOICE_AMOUNT

The opposite of --@hide: every column you don’t list is hidden. Handy when a table has dozens of columns and you only care about a few. If none of the listed names match, the directive is skipped rather than hiding everything.

--@column_order — reorder columns

--@column_order: VENDOR_NAME, INVOICE_NUM, INVOICE_AMOUNT

Lists the columns in the order you want them. Use a * to say “and the rest go here”:

--@column_order: VENDOR_NAME, INVOICE_NUM, *, AUDIT_FLAG

• Columns before the * are pinned to the front, in the order you list them.
• Columns after the * are pinned to the end.
• Everything you didn’t mention fills the * slot, keeping its original order.

If you leave out the *, the columns you list go first and everything else follows in its original order.

--@format — format numbers and dates

--@format assigns a display format to one or more columns. It changes how values look — the underlying number or date is untouched, so sorting, copying, and exporting still use the real value.

The value is a comma-separated list of COLUMN = format pairs:

--@format: INVOICE_AMOUNT = currency(USD), INVOICE_DATE = date(YYYY-MM-DD), TAX_RATE = percent

Commas inside parentheses don’t split the list, so multi-part formats stay intact. Any pair that’s malformed, names a missing column, or uses an unknown format is skipped — the rest still apply.

Number formats

Format	Example
plain	1234.5678
number (same as thousands)	1,234.57
thousands	1,234.57
number(0dp)	1,235
number(2dp)	1,234.57
number(4dp)	1,234.5678
percent	12.34%
scientific	1.23e+4

percent follows the usual convention of multiplying by 100, so a stored value of 0.1234 shows as 12.34%.

Currency formats

Use currency(CODE), or just currency on its own to default to USD. The number of decimal places follows each currency’s convention automatically (yen shows none, most others show two).

Format	Example
currency(USD)	$1,234.57
currency(EUR)	€1,234.57
currency(GBP)	£1,234.57
currency(CAD)	CA$1,234.57
currency(AUD)	A$1,234.57
currency(JPY)	¥1,235
currency(INR)	₹1,234.57

Date formats

Use date(FORMAT), or just date to default to YYYY-MM-DD. Data Collage understands dates that come back in ISO (2025-11-15) or Oracle (15-NOV-2025) shapes; anything it can’t read as a date is left untouched.

Format	Example
date(YYYY-MM-DD)	2025-11-15
date(YYYY/MM/DD)	2025/11/15
date(MM/DD/YYYY)	11/15/2025
date(DD/MM/YYYY)	15/11/2025
date(DD-MMM-YYYY)	15-NOV-2025
date(YYYY-MM-DD HH:mm:ss)	2025-11-15 14:30:00
date(relative)	3 days ago

Putting it all together

--@title: Top AP Invoices by Supplier
--@hide: ORG_ID, CREATED_BY
--@column_order: VENDOR_NAME, INVOICE_NUM, INVOICE_AMOUNT, INVOICE_DATE, *
--@format: INVOICE_AMOUNT = currency(USD), INVOICE_DATE = date(DD-MMM-YYYY)

SELECT s.vendor_name,
       i.invoice_num,
       i.invoice_amount,
       i.invoice_date,
       i.org_id,
       i.created_by
FROM   ap_invoices_all i
JOIN   poz_suppliers s ON s.vendor_id = i.vendor_id
WHERE  i.invoice_amount > 10000
ORDER BY i.invoice_amount DESC

This run renames the tab, hides ORG_ID and CREATED_BY, pins the four key columns to the front, and formats the amount as US dollars and the date as DD-MMM-YYYY — all without changing the query Fusion actually runs.

Good to know

• Directives re-apply on every run, even when the data comes back identical.
• If you tweak the grid by hand afterward — drag a column, change a format from the menu — your change wins until the next run.
• --@title is the only directive that acts without running the query.
• Directives and Saved Analyses work well together: an analysis stores the resolved formatting separately, so you can use either approach or both.