SQL NOT operator does not work

Question

question

Thomas JR asked Jun 23, '20 anthony.johnson commented Jun 26, '20

SQL NOT operator does not work

Hi folks,

the following SQL-query doesn't work for me (ignore the odd variable names):

Table dummy = Table.query("SELECT SUM(Average*Count) FROM Input_data WHERE Variable = $1 AND Hello_world NOT IN ('Iphone', 'I like cats')", parameter_1);

However, when I remove the NOT operator, the query works fine. I believe the syntax is correct according to FlexSim documentation, so what's going on??

And FYI, I do not use any special characters for my original variable names, only what you'll find in the english alphabet.

Software Version:

FlexSim 20.1.3

flexsim 20.1.3 sql queries

5 |100000

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

Answer 1 · 2020-06-23T15:41:31Z

Benjamin W2 answered Jun 23, '20 anthony.johnson commented Jun 26, '20

Flexsim only supports a subset of SQL commands, and unfortunately, NOT is not one of them. You can see a list of what is supported here:

https://docs.flexsim.com/en/20.1/Reference/DeveloperAdvancedUser/SQLQueries/

Is there a way that you could create an array that your value is IN? You could then pass that array to the query.

· 3

5 |100000

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

anthony.johnson ♦♦ commented · Jun 26, 2020 at 05:07 PM

FYI the NOT operator will be supported in the next feature release, 20.2.

1 ·

Thomas JR commented · Jun 23, 2020 at 06:29 PM

@Benjamin W2 oh well, I actually had a quick look at the documentation but I simply assumed that such an essential operator would be supported, despite not being listed. Luckily in my case it is feasible to instead negate the list of values within the IN clause. However there may be other future cases where it is easier to use the NOT operator. I wonder why the developers chose to leave it out.

0 ·

Benjamin W2 Thomas JR commented · Jun 23, 2020 at 06:31 PM

@Thomas JR, I agree that a NOT would be a great addition. Feel free to post an idea. Our dev team looks at that frequently, and the more people who ask for it, the more likely it will be implemented.

1 ·

Answer 2 · 2020-06-24T07:53:50Z

martin.j answered Jun 24, '20 Thomas JR commented Jun 26, '20

Hi @Thomas JR, I have been frustrated by the missing NOT clause as well, but managed to make a query similar to yours with a little workaround:

Table dummy = Table.query("SELECT SUM(Average*Count) FROM Input_data WHERE Variable = $1 AND 1 <> (Hello_world IN ('Iphone', 'I like cats'))", parameter_1);

The IN keyword will return a true or false if the result is in the specified list/array, so if you specify that you want the result only where it is not true, it is practically the same as having a NOT statement. I admit that it is a bit clunky to read but it works.

· 1

5 |100000

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

Thomas JR commented · Jun 26, 2020 at 07:14 AM

@martin.jensen Nice solution! I'm gonna apply it in my model.

0 ·

question