How do you use Table.query on a global list?

Question

question

IT Asset asked Mar 20 2020 at 7:11 PM Benjamin W2 commented May 18 2020 at 5:43 PM

How do you use Table.query on a global list?

In the SQL Queries section of the user manual it mentions you can use a global list in a from statement. Can someone give me the correct syntax to do this?

Currently I have the following instead of the table query because I could not get the syntax quite right:

string vsCell = "MED";
string vsQuery = "WHERE lsCellName = \"" + vsCell + "\"";
Array pullResult = List("glQbeforePress_MED").pull(vsQuery,0,0);

The goal of this was to find out how many of the tokens on the list with the label lsCellName = "MED".

Can anyone tell me what the equivalent to this would be with Table.query() ?

Software Version:

FlexSim 20.0.2

list lists sql

capture.png (15.8 KiB)

· 2

Benjamin W2 commented · May 18 2020 at 5:43 PM

Hi @A Kamphaus, was Manuel Sinco's or Regan Blackett's or Jörg Vogel's answer helpful? If so, please click the red "Accept" button on one of their answers!

1 ·

IT Asset commented · Mar 25 2020 at 8:41 PM

Here is the code I am trying to use that does not work:

List QbeforePress = List("glQbeforePress_MED");
string vsQuery2 = "SELECT * FROM $1 WHERE lsCellName = \"" + vsCell + "\"";
Table tabResult = Table.query(vsQuery2, QbeforePress);

0 ·

Answer 1 · 2020-04-07T12:55:04Z

Manuel Sinco answered Apr 07 2020 at 12:55 PM Manuel Sinco edited Apr 07 2020 at 12:57 PM

Hello @A Kamphaus,

You were close to the answer, but instead of inputting the Table name as a parameter, you can directly call it in the query, as you showed in the imaged you can do any query for any existing Table. This example may help you:

List QbeforePress = List("glQbeforePress_MED");
string CellName = "MED";
Table ResultQ = Table.query("SELECT * FROM glQbeforePress_MED WHERE lsCellName
= $1", CellName); 
int Counter = ResultQ.numRows;

The variable Counter will tell you how many tokens have the label "MED" as you wanted. If you want to check for another label, you just need to change the CellName variable value.

It's important to recall that you can, only, do queries for existing tables not memory tables that are created during a query like this one. However, you can use the memory table to search for information by looping through it, i.e. by looking for the tokens that apply and then pull them.

Hope it works, if you need any help, let me know.

Manuel.

Answer 2 · 2020-03-20T20:18:52Z

Regan Blackett answered Mar 20 2020 at 8:18 PM Regan Blackett edited Mar 25 2020 at 8:19 PM

You could do this with just a regular Pull from List activity in process flow if you set your "Request" and "Require" values to zero and use the Query:

WHERE IsCellName = "MED".

This will put all the Entries of the list that match the query into the token.pulled array but will leave the entries on the list. Then if you want to know many meet the criterial, you can use token.pulled.length.

· 5

IT Asset commented · Mar 20 2020 at 8:31 PM

This is in a decision activity before my pull activity because I use the quantity result (and a couple other things I left out for simplicity sake here) to decide what to pull next. I already have a work around, but I would like to know how to do this with Table.query(). The manual says you can but it doesn't give an example so I'm getting stumped on exact the syntax.

0 ·

Regan Blackett ♦ IT Asset commented · Mar 20 2020 at 9:25 PM

Ok, this will query your global list put the results into an Array (since that's what it looked like you were doing before).

string vsCell = "\"MED\"";
string vsQuery = "SELECT ARRAY_AGG(value) FROM glQbeforePress_MED WHERE IsCellName = " + vsCell;
Array result = Table.query(vsQuery)[1][1];

2 ·

IT Asset Regan Blackett ♦ commented · Mar 25 2020 at 7:53 PM

No I need the results in a table, an array is what I have to use to access list pulls in the code. Please don't accept answer for me @Matthew Gillespie

0 ·

Show more comments

Answer 3 · 2020-03-26T01:49:46Z

Joerg Vogel answered Mar 26 2020 at 1:49 AM Joerg Vogel commented Mar 26 2020 at 1:55 AM

I build my own global list in the attached model.

I check if I can get this list as a table queried.

Table myTable = Table.query("SELECT * FROM myList");

Then I dump it to global.

myTable.cloneTo(Table("dump"));

If I succeed I can start to count in another query.

Table my2ndTable = Table.query(
"SELECT COUNT(lsCellName) FROM myList WHERE lsCellName ='MED'"
);

count-sql-entry-str.fsm

a-global-list.jpg (33.0 KiB)

table-query-list.jpg (27.1 KiB)

count-sql-entry-str.fsm (31.6 KiB)

question