Idea

Cameron Pluim suggested Mar 29 2017 at 3:58 PM Cameron Pluim commented May 16 2017 at 3:13 PM

Be able to dump queries to a Table in memory

I have a situation where I query a global table to get a subset of the original values (Subset A). Then, depending on the value of the first entry on that query, I need to query Subset A in various ways creating Subset B.

In order to accomplish this currently I need to dump Subset A into a "dummy" node so the values are stored somewhere, and then I am able to query Subset A normally. It would be nice if I could do something like the following:

Table myGlobalTable = reftable("GlobalTable1");
 
//Option 1
Table SubsetA = query("SELECT * FROM $1 WHERE Column1 > 2",myGlobalTable);
 
//Option 2
Table SubsetA = nullvar;
query("SELECT * FROM $1 WHERE Column1 > 2",myGlobalTable);
dumpquery(SubsetA);
 
Table SubsetB = nullvar
if(SubsetA[1][2] <= 10) {
	SubsetB = query("SELECT * FROM $1 WHERE Column3 = 2", SubsetA);
} else {
	SubsetB = query("SELECT * FROM $1 WHERE Column3 = 1", SubsetA);
}

This would make it so I don't need to create a "dummy" node that I will be dumping data into, but I would be able to create and store multiple queries within the same code block.

Matt Long commented Mar 29 2017 at 5:29 PM

In 2017 Update 1 you can use the Table.query command to do exactly this, create a table in memory.

· 7

Cameron Pluim commented · Mar 29 2017 at 6:01 PM

That's fantastic!

0 ·

Cameron Pluim commented · May 09 2017 at 3:31 PM

@Matt Long, I just updated to 17.1.2, and I tried using a table in memory, and it doesn't seem to work with column references.

In the attached model, there are 2 script windows. In the first I am directly querying the tables in memory, and it throws errors saying:

Reference to Col 2 could not be resolved to a valid column. Parsing halted.

In the second script window I'm cloning the tables in memory to dummy nodes, and I'm able to query from the node correctly.

tableinmemory.fsm

0 ·

tableinmemory.fsm (12.9 KiB)

Matt Long Cameron Pluim commented · May 15 2017 at 1:56 PM

@anthony.johnson

0 ·

anthony.johnson ♦♦ Cameron Pluim commented · May 15 2017 at 7:49 PM

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()

Although this might not pertain to your case, you can nest queries in 17.1. This would solve your test case, but not necessarily the original question case.

Table result = Table.query("SELECT * FROM (SELECT * FROM (SELECT * FROM $1 WHERE [Col 3] = 1) WHERE [Col 2] = 1) WHERE [Col 1] = 1", Table("GlobalTable1"));

I do believe you could solve your original question somehow, though, with a nested query

0 ·

Cameron Pluim anthony.johnson ♦♦ commented · May 15 2017 at 9:07 PM

Thanks @anthony.johnson. I didn't see that documentation, but it does spell it out pretty clearly. Should I write this up as an idea to be implemented in the future, or are there already plans to be able to do this in the future?

Also, the nested queries look really useful, but I think I'll probably end up using clone.To to a dummy node more often than nested queries from a readability stand point (depending on the table size).

I had actually set up the second test case to see what would be faster, a single query with multiple WHEREs, or multiple queries each with a single WHERE but on tables of decreasing size. But since the multiple queries would need to be copied to their own dummy nodes it made single query significantly faster.

1 ·

Show more comments

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Idea

Be able to dump queries to a Table in memory

1 Comment

Things to know…

Your Opinion Counts

IDEA DETAILS

Related Ideas

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.

Idea

Be able to dump queries to a Table in memory

1 Comment

Things to know…

Your Opinion Counts

IDEA DETAILS

Related Ideas

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

THIRD PARTY SERVICES

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.