cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bjbreitling
Level IV

Script code for join between

Hi I know you can join tables with JMP. How would I do a join between. In SQL server its something like 

SELECT * FROM [L].[dbo].[P] P
INNER JOIN [L].[dbo].[FPD] FPD ON
P.[Join Date] BETWEEN FPD.[BeginDate] and FPD.[EndDate]

 

 

The script builder gives me code like the below with what EQ probably stands for equals. THe between part I added and am trying to figure out the correct way if possible to do in JMP so I don't have to load these tables into sql.

From(
Table( "pH Kinetics", Alias( "t1" ) ),
Table(
"New Dataset for Analysis Final 6",
Alias( "t2" ),
Join(
Type( Left Outer ),
EQ( Column( "Ferm", "t2" ), Column( "FERM", "t1" ) ) &
EQ( Column( "Plant", "t2" ), Column( "Plant", "t1" ) )&
Between( Column( "Time", "t2" ), Column( "BeginTime", "t1" ), Column( "EndTime", "t1" ) )
)

1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User (Alumni)

Re: Script code for join between

Below is an example script that shows two methods for specifying a BETWEEN condition. 

Names Default to Here(1);

//event has a Time variable and really only has events for Entity SPUTTER01
dt1 = Open("C:/temp/event.jmp", Invisible);
//tracker has start times, MoveIn, and end times, MoveOut for many entities 
dt2 = Open("C:/temp/tracker.jmp", Invisible);

//The final result should only have SPUTTER01 data
dt3 = Query(
 Table(dt1, "t1" ), Table(dt2, "t2" ),
    "SELECT t1.Lot, t1.NWafers, t1.OPERATION, t1.SPC_WFRCOUNT, 
		t1.PM_WFRCOUNT, t1.Time,  t2.Entity, t2.SubEntList, t2.MoveIn, t2.MoveOut 
	FROM t1 
		LEFT OUTER JOIN t2 
			ON  (  ( t1.Lot = t2.Lot )  
			AND  ( t1.NWafers = t2.NWafers )  
			AND  ( t1.OPERATION = t2.Operation )  )  
	       WHERE  (  (  ( 1 = 1 )  AND  ( 1 = 1 )  
	       AND  ( t1.Time >= t2.MoveIn and t1.Time <= t2.MoveOut )  )  )"
	);
	
	
	
dt4 = Query(
 Table(dt1, "t1" ), Table(dt2, "t2" ),
    "SELECT t1.Lot, t1.NWafers, t1.OPERATION, t1.SPC_WFRCOUNT, 
		t1.PM_WFRCOUNT, t1.Time,  t2.Entity, t2.SubEntList, t2.MoveIn, t2.MoveOut 
	FROM t1 
		LEFT OUTER JOIN t2 
			ON  (  ( t1.Lot = t2.Lot )  
			AND  ( t1.NWafers = t2.NWafers )  
			AND  ( t1.OPERATION = t2.Operation )  )  
	       WHERE  (  (  ( 1 = 1 )  AND  ( 1 = 1 )  
	       AND  ( t1.Time BETWEEN t2.MoveIn and t2.MoveOut )  )  )"
	);

 

View solution in original post

1 REPLY 1
gzmorgan0
Super User (Alumni)

Re: Script code for join between

Below is an example script that shows two methods for specifying a BETWEEN condition. 

Names Default to Here(1);

//event has a Time variable and really only has events for Entity SPUTTER01
dt1 = Open("C:/temp/event.jmp", Invisible);
//tracker has start times, MoveIn, and end times, MoveOut for many entities 
dt2 = Open("C:/temp/tracker.jmp", Invisible);

//The final result should only have SPUTTER01 data
dt3 = Query(
 Table(dt1, "t1" ), Table(dt2, "t2" ),
    "SELECT t1.Lot, t1.NWafers, t1.OPERATION, t1.SPC_WFRCOUNT, 
		t1.PM_WFRCOUNT, t1.Time,  t2.Entity, t2.SubEntList, t2.MoveIn, t2.MoveOut 
	FROM t1 
		LEFT OUTER JOIN t2 
			ON  (  ( t1.Lot = t2.Lot )  
			AND  ( t1.NWafers = t2.NWafers )  
			AND  ( t1.OPERATION = t2.Operation )  )  
	       WHERE  (  (  ( 1 = 1 )  AND  ( 1 = 1 )  
	       AND  ( t1.Time >= t2.MoveIn and t1.Time <= t2.MoveOut )  )  )"
	);
	
	
	
dt4 = Query(
 Table(dt1, "t1" ), Table(dt2, "t2" ),
    "SELECT t1.Lot, t1.NWafers, t1.OPERATION, t1.SPC_WFRCOUNT, 
		t1.PM_WFRCOUNT, t1.Time,  t2.Entity, t2.SubEntList, t2.MoveIn, t2.MoveOut 
	FROM t1 
		LEFT OUTER JOIN t2 
			ON  (  ( t1.Lot = t2.Lot )  
			AND  ( t1.NWafers = t2.NWafers )  
			AND  ( t1.OPERATION = t2.Operation )  )  
	       WHERE  (  (  ( 1 = 1 )  AND  ( 1 = 1 )  
	       AND  ( t1.Time BETWEEN t2.MoveIn and t2.MoveOut )  )  )"
	);