Skip to content

toscalar-function.md

Latest commit

 

History

History
194 lines (141 loc) · 5.89 KB

toscalar-function.md

File metadata and controls

194 lines (141 loc) · 5.89 KB
title toscalar()
description Learn how to use the toscalar() function to return a scalar constant value of the evaluated expression.
ms.reviewer alexans
ms.topic reference
ms.date 03/09/2025

toscalar()

[!INCLUDE applies] [!INCLUDE fabric] [!INCLUDE azure-data-explorer] [!INCLUDE monitor] [!INCLUDE sentinel]

Returns a scalar constant value of the evaluated expression.

This function is useful for queries that require staged calculations. For example, calculate a total count of events, and then use the result to filter groups that exceed a certain percent of all events.

Any two statements are separated by a semicolon.

Syntax

toscalar(expression)

[!INCLUDE syntax-conventions-note]

Parameters

Name Type Required Description
expression string ✔️ The value to convert to a scalar value.

Returns

A scalar constant value of the evaluated expression. If the result is a tabular, then the first column and first row is taken for conversion.

Tip

You can use a let statement for readability of the query when using toscalar().

Limitations

toscalar() can't be applied on a scenario that applies the function on each row. This is because the function can only be calculated a constant number of times during the query execution. Usually, when this limitation is hit, the following error is returned: can't use '<column name>' as it is defined outside its row-context scope.

In the following example, the query fails with the error:

'toscalar': can't use 'x' as it is defined outside its row-context scope.

let _dataset1 = datatable(x:long)[1,2,3,4,5];
let _dataset2 = datatable(x:long, y:long) [ 1, 2, 3, 4, 5, 6];
let tg = (x_: long)
{
    toscalar(_dataset2| where x == x_ | project y);
};
_dataset1
| extend y = tg(x)

This failure can be mitigated by using the join operator, as in the following example:

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

let _dataset1 = datatable(x: long)[1, 2, 3, 4, 5];
let _dataset2 = datatable(x: long, y: long) [1, 2, 3, 4, 5, 6];
_dataset1
| join (_dataset2) on x 
| project x, y

Output

x y
1 2
3 4
5 6

Additional mitigation patterns for real-world scenarios

In many practical scenarios, you may want to compute a scalar value per row using an expression that performs its own aggregation, such as:

| extend result = toscalar(T | where Key == key | summarize max(Value))

This pattern fails because toscalar() cannot be evaluated once per row. Use one of the supported mitigation patterns below.

  1. Pre-aggregate the data once and then join the aggregated results back to the main table for improved efficiency.
let summary =
    T
    | summarize maxValue = max(Value) by Key;

Dataset1
| join kind=leftouter summary on Key
| project Key, maxValue
  1. Use arg_max() to retrieve the row with the highest value. This is useful when you need both the maximum value and the associated columns.
let summary =
    T
    | summarize arg_max(Timestamp, *) by Key;

Dataset1
| lookup summary on Key
  1. Use a lookup for key/value mappings to avoid row-context violations and ensure efficient dimension-table lookups.
let lookupTable =
    T | summarize maxValue = max(Value) by Key;

Dataset1
| lookup lookupTable on Key
  1. Use window functions or make-series for time-window aggregations
Dataset1
| make-series maxValue = max(Value)
      on Timestamp
      from ago(1h) to now()
      step 1m
      by Key

Examples

The examples in this section show how to use the syntax to help you get started.

Set range for evaluation

Evaluate Start, End, and Step as scalar constants, and use the result for range evaluation.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

let Start = toscalar(print x=1);
let End = toscalar(range x from 1 to 9 step 1 | count);
let Step = toscalar(2);
range z from Start to End step Step | extend start=Start, end=End, step=Step

Output

z start end step
1 1 9 2
3 1 9 2
5 1 9 2
7 1 9 2
9 1 9 2

Generate fixed and dynamic GUID

The following example shows how toscalar() can be used to generate a fixed guid, calculated precisely once, and dynamic values for guid.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

let g1 = toscalar(new_guid());
let g2 = new_guid();
range x from 1 to 2 step 1
| extend x=g1, y=g2

Output

x y
e6a15e72-756d-4c93-93d3-fe85c18d19a3 c2937642-0d30-4b98-a157-a6706e217620
e6a15e72-756d-4c93-93d3-fe85c18d19a3 c6a48cb3-9f98-4670-bf5b-589d0e0dcaf5

Related content