question

martin.j avatar image
1 Like"
martin.j asked martin.j commented

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?

FlexSim 17.2.2
querysqltables
table1.jpg (91.7 KiB)
table2.jpg (5.5 KiB)
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

·
Jacob Gillespie avatar image
2 Likes"
Jacob Gillespie answered martin.j commented

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

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

martin.j avatar image martin.j commented ·

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

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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