Confused Query Builder or confused user?

[#1]

As with JMP 17 I am finally able to run the Query Builder without it constantly crashing (there was an unresolved ticket with JMP Tech Support), I really started using it to relatively great effect. However, it is not making some of the selections I tell it to do. This means I have to copy the script and modify it manually to reflect my selections.

See the below screenshot for example:

• "Esterified Astaxanthin" was added twice with capital A in astaxanthin, although there should be "Esterified Astaxanthin" and "Esterified astaxanthin"
• "Phospholipid estimate (%)" is part of the query although I haven't selected it
• "Phosphatidylcholines" is completely missing, as are "Phospholipids"

There are actually quite a few additional mismatches between what is selected in the Query Builder and what is copied to the script. I am wondering, have other users experienced the same?

[#2]

I just tried it (first time for everything!) and didn't see the issue with a trivial test. Is there a way to reproduce the problem with something small? Was it your thought that mixed case was involved? Or values that are prefixes of other values?

It does seem likely there might be an issue with the list box control and some of those possibilities, but some of your examples don't look like that.

[#3]

Thank you for responding. Mixed cases are definitely involved. I am loading a table that contains at least some legacy data with mixed cases, including "Astaxanthin" and "astaxanthin", for example. But the issue also is that selections are made that I did not make (e.g., "Peroxide_miliEqPerKg") or selections that I did make are not included in the SQL (eg. "Phospholipids").

Unfortunately, at this precise moment, and possibly until after July, I won't have the time to develop different test cases. I was guessing that this might have something to do with the number of rows in the database (>1 million), but I am probably mistaken.

However, if I modify the SQL later to my own need, it works as expected.

[#4]

Greetings @Ressel. We've tried looking at the steps you described and the symptoms and can't see exactly what you are seeing. If you do happen to have time later, any additional test cases you can provide would be helpful to us.

Can you share which database you are using? We have seen some oddities with how different databases store and return duplicate column names that differ only by case. With SQL Server and MySQL, for example, columns named "ABC" and "abc" will only appear as one column in the Query Builder list. In contrast, if connecting to an Oracle database, Query Builder will show both columns in the column list. This may or may not be related to your exact issue. We are investigating further. Thanks very much for bringing your issue here, and if you have further information for us after July 1, we'll gladly use it.

[#5 · Accepted solution]

Hi and thank you for looking at this. I am connecting to Google BigQuery via Simba ODBC. Below, I am adding a few screenshots from inside the Query Builder.

nr. 1:  see how the selection panel to the right correctly displays "Esterified Astaxanthin" and "Esterified astaxanthin" and how this is inaccurately transcribed to the SQL. No other selections were made for this example.

nr. 2: this was interesting. In this screenshot you can see that when I select "Phosphatidylcholines", "Phosphatidylcholines(2)" is written to the SQL. You can see by the red indices I have added that the selection above the one I have made is written to the SQL. You can see this even better in screenshot nr. 3 below.

nr. 3: here, I have deliberately only selected every other parameter. These are now not written to SQL. In screenshot nr. 4 I have tested whether the occurrence of "Esterified Astaxanthin" and "Esterified astaxanthin" are the root cause.

nr. 4: "Esterified Astaxanthin" and "Esterified astaxanthin" appear to be the root cause.