Identifiers
Name tables, columns and variables the way the data names them, and HTQL finds them.
Syntax
An identifier starts with a letter or an underscore, and continues with letters, digits and underscores. A dot joins parts.
table_name
alias.column
variable.field.nested_field
variable.list.0.field
variable.list.field
variable.list.value
_leading_underscoreParameters and arguments
TableLetters, digits and underscores, in GET, LOOKUP, LEARN and FOR. LOOKUP also accepts it in quotes, in any case
AliasA short name given to a GET's table
ColumnThe field's name as the answer names it, in camelCase
Alias columnThe alias, a dot and the column
Record fieldThe variable, a dot and the field
Nested fieldDots continue into deeper records and JSON columns
Item by positionA whole number after a list is a position, counting from 0
Field of every itemA field name after a list reads that field from every item
Item by valueA value the plain list holds is that item; one it does not hold is null
VariableA letter or underscore, then letters, digits and underscores
Case matters in four places. Keywords such as GET, FILTER and RETURN are written in any case. A table name is read in any case in LOOKUP, and in capitals it is refused in GET. A variable must be spelled exactly. A column is found by its name in any case: contentType, ContentType and CONTENTTYPE are one field, so write it as the answer names it.
Examples
Fields written as the answer names them, and the names the result uses.
Return type and behaviour
Write a field in a statement by the name the answer gives it, in camelCase: FILTER(e.contentType = "navLink") reads the field that comes back as contentType. Every name in a result is camelCase, and a name you choose in RETURN or AS is converted the same way, so "orderTotal" comes back as orderTotal and "kind_of_link" as kindOfLink. A name without an underscore comes back as you wrote it.
A path that reaches nothing returns null: a field the record does not have, a position past the end of a list, or a value the list does not hold. A field read across a list returns the values found, in order. A list with one match returns that one value.
Money, percentages, ratios and decimals
A statement always speaks in the units people use, and HTQL converts at the edge. A money field is written in major units (12.34), a percentage as a plain percentage (5 is five percent) and a ratio as a plain decimal (0.25). The database stores each as a whole number so that it never drifts: the engine's own documented examples turn 12.34 into 1234 for a currency with two decimal places, and 5 percent into 500 basis points. You never write those stored integers, and the answer never shows them.
| Kind | You write | The answer returns | How it is stored |
|---|---|---|---|
| Money | Major units, as in FILTER(l.lineTotal > 50) | A record {"amount": …, "currency": …} | A whole number of the currency's smallest unit, rounded half away from zero when a literal has more places than the currency |
| Percentage | A plain percentage, 25 for 25 percent | A plain number in percent | A whole number of basis points |
| Ratio | A plain decimal, 0.25 | A plain decimal, never rounded | A whole number scaled by 10^16 |
| Plain decimal | A decimal such as 0.1 | The full floating point value | A floating point number |
A currency's number of decimal places comes from the business's currency settings: 0 for JPY, KRW and VND, 2 for AUD, USD, EUR and GBP, and 3 for KWD, BHD and OMR. A currency with no minor unit has no decimal point in the stored whole number and none in the amount.
On the way out, each money value becomes {"amount": …, "currency": …}. The amount is a plain number in major units, not text and not rounded. The currency is the one named by a currency field in the same record, and the business's own currency when the record has none. When that field is in the record it is consumed into every amount, so it does not appear a second time beside them. A percentage is a plain number, and a ratio a plain decimal. A stored value that cannot be read as a whole number answers null with a notice in the response, never a silent zero.
A computed value keeps what it measures. The average of a money column is money, and the average of a percentage column is a percentage, whatever key you give the answer, and the average keeps its fraction instead of being forced to a whole smallest unit. Arithmetic on plain numbers stays floating point and returns its full value.
Note
Write major units and plain percentages. Never write a stored integer such as cents or basis points, and never divide or multiply by 100 yourself: the conversion is HTQL's, so a hand written scale is applied twice.
Tip
Never round for display. Return the full value and let the screen choose the places to show. A rounded answer cannot be added up later without losing the pennies the rounding threw away.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-2001 | A field name does not exist on the table, or two fields would answer to it | Use the field's name as the answer names it, or LIST TABLES to see the fields |
HTQL-1004 | A GET table is quoted | Write the name without quotes |
HTQL-3003 | A table name is not one HTQL serves, or a GET table is written in capitals | Check the name, and write it in lower case |
HTQL-1003 | A variable name starts with a digit or holds a hyphen | Start with a letter or an underscore, and use underscores |
Limitations
- Not supported: a quoted table name in GET, such as
GET("entries"). WriteGET(entries, e). - Not supported: a hyphen in a name, such as
my-varor a path step likefields.name.en-US. Name variables with underscores, and read the whole field,e.fields.name, when a key holds a hyphen. - A name read before any LET has bound it is null. LET binds in order, from the top of the statement.
Best practices
- Write the same camelCase names in statements that you read in results.
- Give an alias to a table you read with GET and RETURN.
- Choose a result name in camelCase, so it comes back as you wrote it.
- Reach into JSON with a path instead of fetching the whole document and walking it in code.
- Write money in major units and percentages as plain percentages; return values whole, never rounded or scaled for display.
- Return a row's
currencybeside its amounts when rows can differ, so each amount names its own currency.