Call a row in SQL command by RowHeader?

Question

question

Jouky D asked Oct 4, '23 Jouky D commented Oct 4, '23

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

Software Version:

FlexSim 23.2.1

global table sql sql queries table.query

Answer 1 · 2023-10-04T13:01:47Z

Jason Lightfoot answered Oct 4, '23 Jouky D commented Oct 4, '23

You can try using ROW_NUMBER as described in the documentation.

· 3

Jouky D commented · Oct 04, 2023 at 01:18 PM

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 ·

Jason Lightfoot ♦ Jouky D commented · Oct 04, 2023 at 01:36 PM

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 ·

row-number-group-by-examples.fsm (30.5 KiB)

Jouky D Jason Lightfoot ♦ commented · Oct 04, 2023 at 01:47 PM

Thank you!:D

0 ·

question