Script code for join between

bjbreitling

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" ) )
)

gzmorgan0

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 )  )  )"
	);