Nested Query example using ARRAY_AGG and ROW_NUMBER()

Question

question

Raul V asked Apr 13, '23 Raul V commented Apr 14, '23

Nested Query example using ARRAY_AGG and ROW_NUMBER()

Hi Team, Im doing a scheduling plan and i need to get a nested query where I can get an array of values and another array where i can get the row from i took the previous values, im assuming that would be 2 queries and i tried to create the first one.

Please see below:

Table results=Table.query("SELECT ARRAY_AGG([Qty(01/04/2023)]) AS total FROM GlobalTable1 WHERE [Qty(01/04/2023)]!=0 AND (([***]) !='D'OR'A'))";`
return results[1][1];


//Expected Result (40,30,5)
//And another query that told me from what rows what taken the first array like (2,7,10)

Could you help me to modify the example please?

Thanks a lot for the support!

Model and Excel attached for reference

Normal query
nested that does not work
excel where i show the expected result

NestedQuery.fsm

QueryEnexcel.xlsx

RegularQuery.fsm

Software Version:

FlexSim 23.1.1

sql array row number nested

flexsimquestion.png (41.0 KiB)

nestedquery.fsm (25.4 KiB)

queryenexcel.xlsx (9.7 KiB)

regularquery.fsm (25.4 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2023-04-14T06:14:25Z

Felix Möhlmann answered Apr 14, '23 Raul V commented Apr 14, '23

The end of your query (OR 'A') doesn't make sense. Either write multiple conditions with an AND or use the IN operator.

You can get the same result without the ARRAY_AGG operator. The only thing that changes is the format of the result table. When cloning the result table you either get two arrays, one with the values and one with the rows. Or you get an array of tuples (value, row number).

query1.png (17.0 KiB)

query2.png (14.9 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Raul V commented · Apr 14, 2023 at 04:45 PM

Thanks a lot, this is what i was looking for

0 ·

question