How to use LET variable value in an UDF, something like this-
I am getting error as "encountered an unexpected error Ambiguous reference to field ‘abc’ "
WITH CTE1
(
SELECT "MyString" as MyStringToPass
)
SELECT * FROM FN_Test(abc)[0] as Final
LET
abc = (SELECT RAW CTE1[0].MyStringToPass)
Thanks…
Is it possible to use LET inside the for each in below query to get PassedDate_Holder?
select a
from DATABucket t USE KEYS ["Test1" ,"Test2" ]
LET
a= ARRAY(ARRAY {
"job" : Detail.job,
"Mode" : Detail.Mode,
e,
"PassedDate_Holder" : PassedDate_Holder
}
FOR e in Detail.Order END
--LET OrderedDate_Holder = e.OrderDate --(some expression here)
--LET EventRank = ROW_NUMBER() OVER (PARTITION BY Detail.OrderNum ORDER BY e.PassedDate_Holder,e.Sequence)
FOR Detail in FN_GetDetails(t.filedata)[0] END
SELECT a
FROM DATABucket t USE KEYS ["Test1" ,"Test2" ]
LET a = ARRAY ARRAY { "job" : Detail.job,
"Mode" : Detail.Mode,
e,
"PassedDate_Holder" : expression that can use e or Detail or t
}
FOR e in Detail.Order
END
FOR Detail in FN_GetDetails(t.filedata)[0]
END;
The dataset is activity of events and need to Rank it by a logic. So first doing the unnest Detail.Order (by using FOR each) and then calculating Ranking, trying to use LET for ranking. (Do not want to use UNNEST because of some other issue.)
LET EventRank = ROW_NUMBER() OVER (PARTITION BY Detail.OrderNum ORDER BY e.PassedDate_Holder,e.Sequence)
SELECT a
FROM DATABucket t USE KEYS ["Test1" ,"Test2" ]
LET fn_d = FN_GetDetails(t.filedata)[0],
a = (SELECT RAW
(SELECT d.Mode, d.Job, e,
ROW_NUMBER() OVER (PARTITION BY d.OrderNum ORDER BY e.PassedDate_Holder, e.Sequence) AS rank
FROM d.`Order` AS e)
FROM fn_d AS d);
Inner subquery reference same OrderNum from parent even you can avoid All tother PARTITION BY.
If You need across all documents
Between UNNEST and WHERE you can define LET if you want
SELECT Detail.Mode, Detail.Job, e,
ROW_NUMBER() OVER (PARTITION BY Detail.OrderNum ORDER BY e.PassedDate_Holder, e.Sequence) AS rank
FROM DATABucket t USE KEYS ["Test1" ,"Test2" ]
UNNEST FN_GetDetails(t.filedata)[0] AS Detail
UNNEST Detail.`Order` AS e
WHERE ..............;