sql_expressions.malloynb

>>>markdown
## SQL Expressions

_This feature requires the experimental flag `##! experimental{sql_functions}` at the top of your model._

Malloy allows you to call native database functions using `!type`. For example if you wanted to call the duckdb function bit_length that returns a number, in Malloy you couild write `bitlength!number("this string)`.

Sometimes the SQL expression you want to write can't be expressed this way.  For example in DUCKDB, the extract function looks like.

  `extract(part from date)`


In order to make Malloy write an expression like this you can escape to a `sql_<type>` function.  In the string parameter you can reference dimensions using the substitution operator `${dimension_name}`.

SQL functions are

```
sql_string
sql_number
sql_date
sql_boolean
```
>>>malloy
##! experimental{sql_functions}
source: flights is duckdb.table('../data/flights.parquet') extend {
    dimension:
        dep_year is sql_number(""" extract('year' from ${dep_time}) """)
    measure: flight_count is count()
}
>>>malloy
run: flights -> dep_year + flight_count
>>>markdown
## Reference the SQL Alias

Sometimes you want to access a field directly in SQL, for example, arrays are unnested when referenced in Malloy.  You might want to access a column directly. In this case `${TABLE}` returns the alias for `flights2` in the SQL query.
>>>malloy
source: flights2 is duckdb.table('../data/flights.parquet') extend  {
  dimension:
    c is sql_string(""" ${TABLE}.carrier """)
  measure: flight_count is count()
}

run: flights2 -> c + flight_count