Idea

Axel Kohonen avatar image
0 Likes"
Axel Kohonen suggested Axel Kohonen commented

Adding the original row in the SQL query table to the dumpquery table by default/optional

Hi,

When using SQL queries I generally dump the results into a table using the dumpquery command and then access the original table by getquerymatchtablerow. Sometimes elsewhere in the model another SQL query is made before the getquerymatchtablerow is called and thus the function fails to give the correct row. To get around this I need to call getquerymatchtablerow for all the rows returned by the query immediately after the query was made and put them into the query table into a new column. This seems like an extra step that could be included in the dumpquery function itself.

So my proposal is that you would develop the dumpquery to return the rows in the original table where the query rows where found and insert them into the result table, e.g. into a column added to the end of the table. This could be optional by adding a third optional parameter to the dumpquery command. Thus older models would not break.

How does this sound`?

Axel

querysqldumpquerygetquerymatchtablerow
· 2
5 |100000

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

Cameron Pluim avatar image Cameron Pluim commented ·

I try to set up my original table with a column called "RowNum" that is set to be equal to the row num. Then when I query the table, I just make sure to bring that column as well so that I can access the row from the original table.

2 Likes 2 ·
Steven Hamoen avatar image Steven Hamoen commented ·

I like the idea but it is not applicable for everything. For instance with join queries you will either have 1 or 2 rows and which is which etc. But for standard queries over 1 table this would be a nice addition

1 Like 1 ·

1 Comment

·
anthony.johnson avatar image
2 Likes"
anthony.johnson commented

The problem I see with this is that it doesn't scale well for inner joins. Do you add two columns to the result if it's an inner join of two tables, and do you automatically name them based on the table name, and rank them based on their rank in the FROM? I guess to me this solution just isn't in keeping with the design goals of SQL. It's just weird that we would haphazardly add columns to the result table. Shouldn't that be encoded into the SQL query itself? I've searched several times online to see if there is some SQL-standard keyword that you can use to get the row of the table. It looks like there isn't, likely because usually each row has a unique id field, and you always go off of that id, so you don't need to worry about a row number. If you follow that unique id guideline, you can always add a column to your source table that is just there to tell you what row it is. The other option is to use FlexSim's custom $ syntax to essentially do it yourself.

query("SELECT Col1, Col2, $2 AS RowNum FROM $1", reftable("GlobalTable1"), getrank($iter(1)));

The problem with this solution is that it doesn't work if you have a bundle table (getrank($iter(1)) doesn't work).

At this point I'm not convinced we should add this as a parameter of dumpquery(). My preference would be to get $iter() to work somehow for bundle tables, and/or to add a FlexSim-specific SQL keyword that gets the table row. It seems the broader SQL community doesn't make much stink about not having a keyword, but in FlexSim, lots of interfacing with tables has to do with accessing tables via row numbers, so maybe our situation is different.

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

Axel Kohonen avatar image Axel Kohonen commented ·

Hi @anthony.johnson

Thank you for the input. That makes sense, did not think about the inner joins. Your suggestion about adding the query it to SQL itself is good, and also adding a keyword in FlexSim could be nice. It would probably be easier to use than the iter syntax.

Will try to use the iter syntax as I am generally using the standard global tables and not the bundle ones.

Kind regards,

Axel

0 Likes 0 ·

Write a Comment

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

Your Opinion Counts

Share your great idea, or help out by voting for other people's ideas.