cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
peterrow
Level II

Unexplainable SQL Result

I know!  SQL can be strange and throw up peculiar results at times, but this is really simple SQL with an allegedly simple result.

 

What I have is 3 tables.  

Table 1:  One column, which is a list of dates covering every day between 31.12.2019 and 31.12.2023.

Table 2:  Two columns:  Column 1 is the date of an event and column 2 is the count of the number of times the event occurred on the date in column 1.

Table 3:   Two columns:  Column 1 is the date of a different event and column 2 is the count of the number of times the event occurred on the date in column 1.

 

I want to combine the three tables into a three-column table (date, occurrences of event 1, occurrences of event 2).

 

SQL (it doesn't get much simpler)

 

SELECT t1.Date, t2."N Rows", t3."N Rows" AS "N Rows 2" 
FROM Date_to_2024  t1 
	LEFT OUTER JOIN QNs_Opened t2 
		ON  ( t1.Date = t2."Created On" )  
	LEFT OUTER JOIN QNs_Closed t3 
		ON  ( t1.Date = t3."Completn date" ) 

 

Table 1:  (Plenty of Data occurring after 10/08/2021)

peterrow_2-1671702106930.png

Table 2:   (Plenty of Data occurring after 10/08/2021)

peterrow_3-1671702317343.png

Query Result

 

peterrow_1-1671701862769.png

Data from table 1 ("Created on",  "N Rows 2") - Correct.

Data from table 2 ("Completn Date", "N Rows") - Correct until 10/08/2021, then no further data.

 

I've tried switching table 1 and table 2.  I've tried importing the data again and re-running the query.  I have recreated the query from scratch...no luck.

 

Any ideas?

 

 

 

 

 

 

5 REPLIES 5
peterrow
Level II

Re: Unexplainable SQL Result

Incidentally, I have solved the problem by using the "Join Table" function (Join two of the tables and then join the third to the result).  That worked.

 

I'd like to know what is wrong with the SQL though!

Re: Unexplainable SQL Result

Weird. If I run your exact query, I get what you're expecting. What version of JMP are you on?

 

Jed_Campbell_0-1671727642894.png

 

jthi
Super User

Re: Unexplainable SQL Result

@Jed_Campbell you have to go more down, it is 10th of August 2021 when the issue occurs (or after it)

jthi_0-1671729628646.png

I also tried checking with jmps datetime values and still the same issue

jthi_1-1671729851434.png

 

QNs_Closed.jmp has one empty row, after removing it the query started working properly. No idea why.

jthi_2-1671730033431.png

jthi_3-1671730077944.png

You can see this happening in the preview, create query with the row still there, build query, open review, delete the row and update preview

 

 

 

-Jarmo

Re: Unexplainable SQL Result

Well that is curious (and I duplicated it). I wonder why Aug 2021 is the date things go astray.

peterrow
Level II

Re: Unexplainable SQL Result

Thanks for the help.  The empty line is expected in the data, but I could delete it with code before running the query.  I'm not sure that should be necessary though.

 

In the future I'll keep an eye out for that but for now, two good and easy workarounds,