SQL query against Table variables return nothing

Question

question

martin.j asked Oct 10, '17 martin.j commented Oct 11, '17

SQL query against Table variables return nothing

I have been trying to query data from a table that exists only as a Table variable in my code. But that seems to return nothing.

Example:
I have a global "OfflineOrders" table and I query it to get a subset of the table. If I then want to further query against that result table I get nothing.

Table allorders = Table("OfflineOrders");
Table OrdersList = Table.query("SELECT * FROM $1 WHERE [SimSku] = 43 OR [SimSku] = 20 OR [SimSku] = 7", allorders);
tab.cloneTo(Table("temptable"));
Table tab = Table.query("SELECT * FROM $1 WHERE [SimSku] = 20", OrdersList);
tab.cloneTo(Table("temptable_2"));

temptable:

temptable2:

If I add a WHERE clause to my second SQL statement if fail all-together and returns an error (Obviously because it cant find any of the columns I am using in the WHERE clause).

I have discovered that if I create a new node and use the CloneTo method to clone my first result to a node and the query that node instead, it seems to work, but that is a rather bulky and cpu consuming method to be able to query results iteratively.

Is this a bug or working as designed?

Software Version:

FlexSim 17.2.2

query sql tables

table1.jpg (91.7 KiB)

table2.jpg (5.5 KiB)

5 |100000

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

Answer 1 · 2017-10-10T17:56:07Z

Jacob Gillespie answered Oct 10, '17 martin.j commented Oct 11, '17

We do not yet support performing queries on result Tables. Here is @anthony.johnson's response to someone with a similar question:

Unfortunately Table.query() was not designed for this specific situation. Instead, the new Table.query() features were meant to allow multiple query results to be active simultaneously, but these were not yet able to be subsequently passed into Table.query() again. See the documentation for p1-p9 of Table.query():

You will usually ... pass a reference to a table node here, defining the table to query ... If this parameter defines a table, it must be a tree-bound table or a direct reference to a node. In other words, it cannot be the result Table of another call to Table.query(), unless you clone that result to a tree-bound table with Table.cloneTo()

· 1

5 |100000

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

martin.j commented · Oct 11, 2017 at 08:43 AM

Oh, RTFM! Sorry about that. I will look more closely at the documentation.

0 ·

question