Scalar functions

Scalar expressions calculate one value for each input row. The functions below are recognized by the workbench. A connected source must support the particular signature too; check compatibility before using source-specific expressions.

All examples query lake.uploads.orders or lake.uploads.imported_orders. Replace these connector.dataset.table names with your catalog entries.

Text cleanup

• LOWER(text) and UPPER(text) standardize case.
• TRIM(text), LTRIM(text), and RTRIM(text) remove surrounding, leading, or trailing whitespace.
• LENGTH(text) and CHAR_LENGTH(text) measure text length according to the executing source.
• SUBSTR(text, start, length) and SUBSTRING select a portion of text.
• REPLACE(text, search, replacement) substitutes matching text.
• CONCAT joins values; CONCAT_WS takes a separator first. Check null handling on your source.

SELECT order_id,
       UPPER(TRIM(country)) AS country_code,
       LOWER(status) AS normalized_status,
       CONCAT(country, ':', status) AS segment
FROM lake.uploads.orders
LIMIT 100;

Keep original identifiers when normalizing display text. Changing case or stripping spaces can merge distinct business keys.

Numeric calculations

• ABS(value): magnitude without its sign.
• ROUND(value, places): round to a requested scale.
• CEIL(value) / CEILING(value): round toward positive infinity.
• FLOOR(value): round toward negative infinity.
• MOD(value, divisor): remainder.
• POWER(value, exponent): exponentiation.
• SQRT(value): square root; inspect negative inputs before applying it.

SELECT order_id, revenue,
       ROUND(revenue, 2) AS rounded_revenue,
       ABS(revenue) AS absolute_revenue,
       FLOOR(revenue / 100.0) AS hundred_unit_band
FROM lake.uploads.orders
LIMIT 100;

Rounding does not change currency. Use decimal inputs where exact financial arithmetic is required and verify the transferred types in results and limits.

Nulls and bounds

COALESCE(a, b, ...) chooses the first available non-null value. NULLIF(a, b) returns null when its arguments are equal. GREATEST and LEAST compare values within a row; their treatment of nulls depends on the execution source.

SELECT order_id,
       COALESCE(country, 'Unknown') AS reporting_country,
       NULLIF(TRIM(status), '') AS nonblank_status
FROM lake.uploads.orders
LIMIT 100;

Null and empty text are different. BLANK_TO_NULL and DEFAULT_IF_BLANK provide FastBI’s explicit blank-handling behavior.

Calendar periods

DATE_TRUNC(part, value) creates a calendar bucket. EXTRACT and DATE_PART retrieve a component. MAKE_DATE(year, month, day) constructs a date from components. Use a reporting-timezone field when the source timestamps need timezone normalization.

SELECT DATE_TRUNC('month', order_date) AS month_start,
       SUM(revenue) AS revenue
FROM lake.uploads.orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start
LIMIT 100;

You can also use CURRENT_DATE, CURRENT_TIMESTAMP, and NOW. Their values and timezone follow the executing source. For reproducible reports, use explicit reporting boundaries.

Conversion and formatting

CAST(value AS type) requests a type conversion and can fail for invalid input. TO_DATE, TO_TIMESTAMP, and TO_CHAR are recognized, but format patterns and signatures depend on the source. Do not assume one connector’s formatting tokens work everywhere.

For imperfect imports, use FastBI’s SAFE_NUMBER and SAFE_DATE functions. Invalid conversions become null, so measure conversion failures before calculating totals.

SELECT order_id,
       SAFE_NUMBER(revenue_text) AS revenue,
       SAFE_DATE(order_date_text) AS order_date
FROM lake.uploads.imported_orders
LIMIT 100;

Safe ratios

Use SAFE_DIVIDE(numerator, denominator) for a ratio and PERCENT(numerator, denominator) for a percentage. Review their null and zero behavior in the function reference.

SELECT SAFE_DIVIDE(SUM(revenue), COUNT(DISTINCT customer_id))
         AS revenue_per_customer
FROM lake.uploads.orders;

Additional functions

The [workbench supported list](/sql/workbench-dialect/#built-in-function-supported list) is the boundary for submitted queries. JSON extraction, regular-expression functions, and provider-specific extensions are not automatically available. Prepare a source view exposing the required fields when an expression is outside this boundary.