topic Re: SQL select for Row values in FlexSim Forum

SQL select for Row values

Raul_V — Wed, 26 Apr 2023 15:45:12 GMT

[ FlexSim 23.1.1 ]

Hi team!

I need to create a select considering a row value (right now i use a for cycle but i would like to use a select statement if is possible)

Could you please help me to create a select considering a row value ? for examples

Select colum header and value from row "X" where value in row is different to "0" ?

//Expected value is Type,2,CT,30,Col5,10,Col6,5

Thanks a lot for the support

QueryforRows.fsm

Re: SQL select for Row values

kavika_faleumu — Wed, 26 Apr 2023 22:16:20 GMT

Hey @Raul V, I'm curious as to why you want to use an SQL Table.query() if you are currently using a working for-loop. The performance of the two is nearly identical (considering the SQL query will still have to loop through the table to perform its analysis). SQL would not be ideal for the kind of output you're looking for because it doesn't have an easy way to query Column Headers, especially based upon value conditionals in each column.

Nevertheless, there are a few SQL Queries you could try to use to accomplish your objective. You could try a combination of ARRAY() and CASE-WHEN-THEN-ELSE-END statements to construct something like this:

SELECT ARRAY(CASE WHEN [Col 1] != 0 THEN 'Col 1' ELSE '' END, ...)

You would have to construct a case for every column in your table - if your table has 50 columns, you're writing 50 CASE statements.

You could also try the MAP() function to group the headers and values in junction with the Select statement above, but I am struggling to see a good solution using it.

Re: SQL select for Row values

joerg_vogel_HsH — Thu, 27 Apr 2023 05:48:03 GMT

Flexscript Class Api Data object Map()

Re: SQL select for Row values

jason_lightfoot_adsk — Thu, 27 Apr 2023 13:00:58 GMT

You would normally use a different table structure for this where your columns are listed as rows in another table.

Re: SQL select for Row values

Raul_V — Thu, 27 Apr 2023 17:46:57 GMT

Hello @Kavika F this is a HMLV model where i control the flow item process flow using a global table and where each type goes directly to any process without doing any connection with "A" , each item have diffetnt cycle time depending each processor .

Every time that i create a flow item i need to to create a loop for get the values from the token ( on item creation) this is why i tried to do it with select.

Do you know a direct way to pass all the labels for the token to a flow item without do loops? maybe i can do it only one and use a select?

Re: SQL select for Row values

jason_lightfoot_adsk — Sun, 30 Apr 2023 23:38:11 GMT

treenode labs=token.labels["item"].up;
labs.copy(labels(token.item),COPY_FLAG_REPLACE);

Re: SQL select for Row values

Raul_V — Wed, 03 May 2023 12:57:57 GMT

Thanks a lot,