Using LET value in FROM in UDF

[Ad]

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)

[vsr1]

The order of execution is

1. WITH
2. FROM/JOIN
3. LET
4. WHERE
5. GROUP BY/Aggregates
6. LETTING
7. Projection
8. ORDER BY
9. OFFSET
10. LIMIT

I above case LET value can’t be used in FROM

WITH CTE1 AS ( SELECT "MyString" as MyStringToPass),
     abc AS (SELECT RAW CTE1[0].MyStringToPass)
SELECT * FROM FN_Test(abc)[0] as Final;

[Ad]

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

[vsr1]

No, Where do u want use OrderDate_Holder ?

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;

[Ad]

Updates the LET in original question.

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)

Input Array-
[
{
OrderNum = 1
,PassedDate = “2023-05-17 15:00:00Z”
,Sequence =1
,…some other properties
},
{
OrderNum = 1
,PassedDate = “2023-06-10 14:00:00Z”
,Sequence =2
,…some other properties
},
{
OrderNum = 25
,PassedDate = “2023-07-01 18:00:00Z”
,Sequence =1
,…some other properties
}
]

Output object will look like this-
OrderNum,PassedDate,Sequence, EventRank
1,“2023-06-10 14:00:00Z”,2,1
1,“2023-05-17 15:00:00Z”,1,2
25,“2023-07-01 18:00:00Z”,1,1

[vsr1]

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 ..............;