Forum Discussion
dcalap
May 08, 2020Copper Contributor
Inject column as parameter in a defined function inside a join
I have a function daysOfMonthNoWeekends which returns a number with a date as parameter. I have a join where I want to use this function in the summarize. Problem: I can't use one of my date ...
dcalap
May 08, 2020Copper Contributor
I've simplified it in order that you see what I mean.
This works (using now() in the function call:
let daysOfMonthNoWeekends=(_event_date_t:datetime) {
toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d
| where dayofweek(days) between(1d .. 5d)
| count)
};
//
MyTable_CL
| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(now())
And this doesn't works:
let daysOfMonthNoWeekends=(_event_date_t:datetime) {
toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d
| where dayofweek(days) between(1d .. 5d)
| count)
};
//
MyTable_CL
| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(TimeGenerated)
//or with another column of MyTable like event_date_t fails too
//| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(event_date_t)
Error:
Semantic error: '' has the following semantic error: Unresolved reference binding: 'TimeGenerated'.
For the record I pretend to add a column with the number of days without weekends in a month based on a column which is a date.
dosuciu
Sep 10, 2021Former Employee
- dcalap Is there a fix for this?? I can't believe kusto does not let you do this!!
- dosuciuSep 10, 2021Former EmployeeIt seems to be By Design: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/functions/user-defined-functions#restrictions-on-the-use-of-user-defined-functions
A user-defined function belongs to one of two categories:
Scalar functions
Tabular functions, also known as views
The function's input arguments and output determine whether it is scalar or tabular, which then establishes how it might be used.
Scalar function
Has zero input arguments, or all its input arguments are scalar values
Produces a single scalar value
Can be used wherever a scalar expression is allowed
May only use the row context in which it is defined
Can only refer to tables (and views) that are in the accessible schema
Tabular function
Accepts one or more tabular input arguments, and zero or more scalar input arguments, and/or:
Produces a single tabular value
This explains why we cannot do this: May only use the row context in which it is defined
As soon as a scalar function tries to make a query beyond the "row context" it will fail.
That kinda sucks!!!