Discussions

Pacco · Mar 20, 2019 01:35 PM

Hey, I am trying to join multiple tables and copied the SQL script from the JMP Query Builder into a script, adding some commands in the beginning. Probably, it doesn´t work as it´s SQL and not JSL language?

My problem is that in JMP Query Builder I do not have the option to decide which columns should be chosen for the matching of the rows. Anybody, who can help me out here?

This is the script I now have. (the combination of JSL and SQL)

dt=CurrentDataTable ();
dt<< SELECT * 
FROM "mon_trend Abs._OF 420nm 5cm"  t1 
	LEFT OUTER JOIN "mon_trend TOC mg l" t20 
		ON  (  ( t20.Site = t1.Site )  AND  ( t20.Season = t1.Season ) )
	LEFT OUTER JOIN "mon_trend Syrgashalt mg l" t19 
		ON  (  ( t19.Site = t1.Site )  AND  ( t19.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Si mg l" t18 
		ON  (  ( t18.Site = t1.Site )  AND  ( t18.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend PO4-P µg l" t17 
		ON  (  ( t17.Site = t1.Site )  AND  ( t17.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend pH" t16 
		ON  (  ( t16.Site = t1.Site )  AND  ( t16.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NO2-N µg l" t15 
		ON  (  ( t15.Site = t1.Site )  AND  ( t15.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NO2+NO3-N µg l" t14 
		ON  (  ( t14.Site = t1.Site )  AND  ( t14.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend NH4-N µg l" t13 
		ON  (  ( t13.Site = t1.Site )  AND  ( t13.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Na mekv l" t12 
		ON  (  ( t12.Site = t1.Site )  AND  ( t12.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Mn µg l" t11 
		ON  (  ( t11.Site = t1.Site )  AND  ( t11.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Mg mekv l" t10 
		ON  (  ( t10.Site = t1.Site )  AND  ( t10.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Kond_25 mS m25" t9 
		ON  (  ( t9.Site = t1.Site )  AND  ( t9.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend K mekv l" t8 
		ON  (  ( t8.Site = t1.Site )  AND  ( t8.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Fe µg l" t7 
		ON  (  ( t7.Site = t1.Site )  AND  ( t7.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend F mg l" t6 
		ON  (  ( t6.Site = t1.Site )  AND  ( t6.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Cl mekv l" t5 
		ON  (  ( t5.Site = t1.Site )  AND  ( t5.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Ca mekv l" t4 
		ON  (  ( t4.Site = t1.Site )  AND  ( t4.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Alk. Acid mekv l" t3 
		ON  (  ( t3.Site = t1.Site )  AND  ( t3.Season = t1.Season ) )  
	LEFT OUTER JOIN "mon_trend Abs._F 420nm 5cm" t2 
		ON  (  ( t1.Site = t2.Site )  AND  ( t1.Season = t2.Season ) )  ;

gzmorgan0 · Mar 20, 2019 6:00 PM

Look up the JMP function Query()

Query(<<dt1|Table(dt1, alias1)>, ..., <dtN, aliasN)>>, <private | invisible>, <scalar>, sqlStatement )

Description

Performs a SQL query on selected data tables.

Of course, you can use JMP Query Builder in a ascript. When you ran the JMP Query Builder script and it ran, the resulting table should have a Run Query script attached. This is the JSL script to run the same query.

Hope that helps.

View solution in original post

gzmorgan0 · Mar 20, 2019 6:00 PM