question

Jouky D avatar image
0 Likes"
Jouky D asked Jouky D commented

Call a row in SQL command by RowHeader?

Hello everyone,

I need to take a row of a Table to an array. For example:


 Col1 Col2
Row1 1 2
Row2 3 4


By indexRow

table[2] --> [3, 4]

Or by RowHeader:

table["Row2"] -- > [3,4]

I know I can obtain the array of a column doing:
Table.query("SELECT ARRAY_AGG([Col2]) FROM table")

However, in SQL the WHERE statement is only for Column. I don't know how to call the RowHeader as a column as:

Table.query("SELECT * FROM table WHERE Row = 2)

Or by RowHeader:
Table.query("SELECT * FROM table WHERE RowHeader = "Col2")

How can we obtain the row into an array? I tought to make a for loop to obtain all values into an array, but maybe SQL statement will be optimal.

Thank you!:D

FlexSim 23.2.1
global tablesqlsql queriestable.query
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

Jason Lightfoot avatar image
0 Likes"
Jason Lightfoot answered Jouky D commented

You can try using ROW_NUMBER as described in the documentation.

· 3
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Jouky D avatar image Jouky D commented ·

Hello @Jason Lightfoot I tried this:

Table tab2 = Table.query("SELECT * FROM table WHERE ROW_NUMBER() = 2");

It returns an error. I can't find examples of ROW_NUMER or SQL Window Functions. I don't know where to apply those functions.

Can you write an example of it please?

As I deduced, the SQL Aggregation Functions are used on columns (e.g.: Table.query("SELECT ARRAY_AGG([Col2]) FROM table")).

But I don't know how to apply: SQL Window Functions, SQL Functions, GROUP BY.

Can you help me on understanding how and where to apply these sort of commands?

Thank you!

0 Likes 0 ·
Jason Lightfoot avatar image Jason Lightfoot ♦♦ Jouky D commented ·

For example:

Table.query("SELECT ROW_NUMBER AS rownum, * FROM GlobalTable1 WHERE ROW_NUMBER=3").cloneTo(Table("resultSet"));

ROW_NUMBER_GROUP_BY_Examples.fsm

0 Likes 0 ·
row-number-group-by-examples.fsm (30.5 KiB)
Jouky D avatar image Jouky D Jason Lightfoot ♦♦ commented ·
Thank you!:D
0 Likes 0 ·