Inject column as parameter in a defined function inside a join

%3CLINGO-SUB%20id%3D%22lingo-sub-1373126%22%20slang%3D%22en-US%22%3EInject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1373126%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20function%26nbsp%3BdaysOfMonthNoWeekends%20which%20returns%20a%20number%20with%20a%20date%20as%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20join%20where%20I%20want%20to%20use%20this%20function%20in%20the%20summarize.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%20I%20can't%20use%20one%20of%20my%20date%20columns%20as%20the%20function%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorking%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20eventTable%20%3D%20Events_CL%3B%0Alet%20customerTable%20%3D%20Customers_CL%3B%0A%2F%2FMyFunction%0Alet%20daysOfMonthNoWeekends%3D(event_date_t%3Adatetime)%20%7B%20%0Atoscalar(range%20days%20from%20startofmonth(event_date_t)%20to%20endofmonth(event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20summarize%20count())%20%7D%3B%0A%2F%2FJoin%20query%0Alet%20usersByDayNoWeekends%20%3D%20eventTable%0A%7C%20join%20customerTable%20on%20(%24left.license_id_d%20%3D%3D%20%24right.license_id_d%20and%20%24left.event_date_t%20%3D%3D%20%24right.datetime_t)%0A%7C%20where%20license_type_s%20%3D%3D%20%22COMMERCIAL%22%0A%20and%20license_status_s%20%3D%3D%20%22VALID%22%0A%20and%20dayofweek(event_date_t)%20between(1d%20..%205d)%0A%7C%20distinct%20name_s%2C%20event_date_t%0A%2F%2F%20if%20I%20put%20daysOfMonthNoWeekends(event_date_t)%20in%20the%20next%20line%20is%20failing.%20Using%20now()%20%20as%20parameter%20works%0A%7C%20summarize%20uniqueUsersByMonth%20%3D%20count()%20%2F%20todouble(daysOfMonthNoWeekends(now()))%20by%20format_datetime(event_date_t%2C%20%22yyyy-MM%22)%0A%3B%0AusersByDayNoWeekends%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20summarize%20line%2C%20in%20the%20line%20before%20you%20can%20see%20what%20I%20want%20to%20do%20but%20is%20not%20working.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGenerated%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ESemantic%20error%3A%20''%20has%20the%20following%20semantic%20error%3A%20Unresolved%20reference%20binding%3A%20'event_date_t'.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BAny%20idea%3F%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1373126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1373264%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1373264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660677%22%20target%3D%22_blank%22%3E%40dcalap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20got%20lost%20with%20the%20JOIN%2C%20couldn't%20you%20simplify%2C%20like%20this%2C%20then%20join%20that%20to%20the%20other%20table%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20Computer%20%3D%3D%20%22RETAILVM01%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofmonth(now())%0A%7C%20where%20dayofweek(TimeGenerated)%20between(1d%20..%205d)%0A%7C%20summarize%20count()%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%2C%20format_datetime(TimeGenerated%2C%20%22yyyy-MM-dd%22)%0A%7C%20order%20by%20TimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1374014%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1374014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20simplified%20it%20in%20order%20that%20you%20see%20what%20I%20mean.%3C%2FP%3E%3CP%3EThis%20works%20(using%20now()%20in%20the%20function%20call%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20daysOfMonthNoWeekends%3D(_event_date_t%3Adatetime)%20%7B%0Atoscalar(range%20days%20from%20startofmonth(_event_date_t)%20to%20endofmonth(_event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20count)%0A%7D%3B%0A%2F%2F%0AMyTable_CL%0A%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(now())%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20this%20doesn't%20works%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20daysOfMonthNoWeekends%3D(_event_date_t%3Adatetime)%20%7B%0Atoscalar(range%20days%20from%20startofmonth(_event_date_t)%20to%20endofmonth(_event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20count)%0A%7D%3B%0A%2F%2F%0AMyTable_CL%0A%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(TimeGenerated)%0A%2F%2For%20with%20another%20column%20of%20MyTable%20like%20event_date_t%20fails%20too%0A%2F%2F%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(event_date_t)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EError%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ESemantic%20error%3A%20''%20has%20the%20following%20semantic%20error%3A%20Unresolved%20reference%20binding%3A%20'TimeGenerated'.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20the%20record%26nbsp%3BI%20pretend%20to%20add%20a%20column%20with%20the%20number%20of%20days%20without%20weekends%20in%20a%20month%20based%20on%20a%20column%20which%20is%20a%20date.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741201%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741201%22%20slang%3D%22en-US%22%3E%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660677%22%20target%3D%22_blank%22%3E%40dcalap%3C%2FA%3E%26nbsp%3BIs%20there%20a%20fix%20for%20this%3F%3F%20I%20can't%20believe%20kusto%20does%20not%20let%20you%20do%20this!!%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741210%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741210%22%20slang%3D%22en-US%22%3EIt%20seems%20to%20be%20By%20Design%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Ffunctions%2Fuser-defined-functions%23restrictions-on-the-use-of-user-defined-functions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Ffunctions%2Fuser-defined-functions%23restrictions-on-the-use-of-user-defined-functions%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EA%20user-defined%20function%20belongs%20to%20one%20of%20two%20categories%3A%3CBR%20%2F%3E%3CBR%20%2F%3EScalar%20functions%3CBR%20%2F%3ETabular%20functions%2C%20also%20known%20as%20views%3CBR%20%2F%3EThe%20function's%20input%20arguments%20and%20output%20determine%20whether%20it%20is%20scalar%20or%20tabular%2C%20which%20then%20establishes%20how%20it%20might%20be%20used.%3CBR%20%2F%3E%3CBR%20%2F%3EScalar%20function%3CBR%20%2F%3EHas%20zero%20input%20arguments%2C%20or%20all%20its%20input%20arguments%20are%20scalar%20values%3CBR%20%2F%3EProduces%20a%20single%20scalar%20value%3CBR%20%2F%3ECan%20be%20used%20wherever%20a%20scalar%20expression%20is%20allowed%3CBR%20%2F%3EMay%20only%20use%20the%20row%20context%20in%20which%20it%20is%20defined%3CBR%20%2F%3ECan%20only%20refer%20to%20tables%20(and%20views)%20that%20are%20in%20the%20accessible%20schema%3CBR%20%2F%3ETabular%20function%3CBR%20%2F%3EAccepts%20one%20or%20more%20tabular%20input%20arguments%2C%20and%20zero%20or%20more%20scalar%20input%20arguments%2C%20and%2For%3A%3CBR%20%2F%3EProduces%20a%20single%20tabular%20value%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20explains%20why%20we%20cannot%20do%20this%3A%20May%20only%20use%20the%20row%20context%20in%20which%20it%20is%20defined%3CBR%20%2F%3EAs%20soon%20as%20a%20scalar%20function%20tries%20to%20make%20a%20query%20beyond%20the%20%22row%20context%22%20it%20will%20fail.%20%3CBR%20%2F%3EThat%20kinda%20sucks!!!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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 columns as the function parameter.

 

Working query:

 

let eventTable = Events_CL;
let customerTable = Customers_CL;
//MyFunction
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)
| summarize count()) };
//Join query
let usersByDayNoWeekends = eventTable
| join customerTable on ($left.license_id_d == $right.license_id_d and $left.event_date_t == $right.datetime_t)
| where license_type_s == "COMMERCIAL"
 and license_status_s == "VALID"
 and dayofweek(event_date_t) between(1d .. 5d)
| distinct name_s, event_date_t
// if I put daysOfMonthNoWeekends(event_date_t) in the next line is failing. Using now()  as parameter works
| summarize uniqueUsersByMonth = count() / todouble(daysOfMonthNoWeekends(now())) by format_datetime(event_date_t, "yyyy-MM")
;
usersByDayNoWeekends

 

In the summarize line, in the line before you can see what I want to do but is not working. 

 

Generated error:

Semantic error: '' has the following semantic error: Unresolved reference binding: 'event_date_t'.

 

 Any idea? Thanks in advance!

4 Replies

@dcalap 

 

I got lost with the JOIN, couldn't you simplify, like this, then join that to the other table? 

 

Heartbeat
| where Computer == "RETAILVM01"
| where TimeGenerated > startofmonth(now())
| where dayofweek(TimeGenerated) between(1d .. 5d)
| summarize count() by Computer, bin(TimeGenerated, 1d), format_datetime(TimeGenerated, "yyyy-MM-dd")
| order by TimeGenerated asc

@Clive Watson 

 

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.

  • @dcalap Is there a fix for this?? I can't believe kusto does not let you do this!!
It seems to be By Design: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/functions/user-defined-functions#re...

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!!!