question

Raul V avatar image
0 Likes"
Raul V asked Raul V commented

SQL select for Row values

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


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


1682523466601.png
  1. //Expected value is Type,2,CT,30,Col5,10,Col6,5


Thanks a lot for the support

QueryforRows.fsm


FlexSim 23.1.1
sqlrowheaderselect
1682523466601.png (11.0 KiB)
queryforrows.fsm (28.7 KiB)
· 1
5 |100000

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

1 Answer

Kavika F avatar image
1 Like"
Kavika F answered Raul V commented

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:

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

· 4
5 |100000

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