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:
- NOT predicate
- LEFT OUTER JOIN
- DISTINCT
- UNION
- 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.
