AnswerHub Upload Issue
Our AnswerHub community currently has a bug that prevents you from attaching your model or other files to your questions. AnswerHub is aware of this issue and they are working on a fix. Until we receive their patch, you can upload your models and other files to FlexSim's file share site. Include the file name in your question so that we know which upload to reference.

Idea

Serge A avatar image
20 Likes"
Serge A suggested anthony.johnson commented

Flexsim SQL Wishlist: NOT predicate, LEFT JOINs, DISTINCT, UNION, CRUD support

Having a subset of SQL SELECT which can work with FlexSim tables and lists is really helpful. Except when you start writing a query and then miss a SQL feature which is not implemented, and then you're back to writing for loops and emulating JOINs.

My personal short list of features I miss the most is:

  1. NOT predicate
  2. LEFT OUTER JOIN
  3. DISTINCT
  4. UNION
  5. and the big one: INSERT/UPDATE/DELETE statements

Rationale:

FlexSim SQL supports only AND and OR operators, but lacks negation. Without negation, this set of operators is not functionally complete. It's not possible to express all predicates with only AND and OR. The workarounds include writing for loops instead of a query, or inventing arcane arithmetic expressions which will evaluate to the desired 0-1. Neither solution is as readable as a SELECT ... WHERE NOT().

LEFT OUTER JOIN would allow to use queries for answer all kinds of question like "what <insert a thing> doesn't have a matching <insert some other thing>?". This is especially frequent in scenarios where there is a 1:0..n relationship between model entities. I don't know any workaround, but writing some code.

DISTINCT has been already asked for, and I admit there is a workaround involving nested queries with HAVING and COUNT, but it would be so much nicer if FlexSim supported it directly.

UNION would allow to easily join multiple data sources together. Especially useful in simulation where each object owns its data, but some high level decisions need all the data together. Currently the workaround is to aggregate all the data in global tables, but in this case adding, changing or removing simulation objects requires updating global tables too. Easy to forget something or do it wrong. If FlexSim SQL supported UNION, the [partial] data could be located where it belongs. Another use case is Calculated Tables. Multiple Statistics Collectors operating on different sets of events may produce similarly structured tables. A UNION in a Calculated Table would be a natural way to merge them. Without UNION, we are constrained to use a single but much more convoluted StatsCollector.

Finally, I understand that implementing the entire CRUD is a big thing to ask, but that would allow to keep working at the same level of abstraction as for reading model data. Without INSERT/UPDATE/DELETE, we may easily read/filter/merge data, but any change throws us back into writing deeply nested for loops. It always feels like a step backward.

sqltablesfeature request
5 |100000

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

2 Comments

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

As of 20.2, all features in this request will be supported, except for SELECT DISTINCT. SELECT DISTINCT has not been high priority because it is a redundant subset of GROUP BY. SELECT DISTINCT A, B, C FROM X is the same as SELECT A, B, C FROM X GROUP BY A, B, C.

5 |100000

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

Jon Abbott avatar image
0 Likes"
Jon Abbott commented Jon Abbott edited

@phil.bobo

@anthony.johnson

@jordan.johnson

I second this highly-upvoted feature request. It would be very useful to have additional SQL JOIN and DISTINCT functionality in Table.query().

5 |100000

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

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.