Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
bjbreitling
Level III

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
Highlighted
gzmorgan0
Super User

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
Highlighted
gzmorgan0
Super User

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

Article Labels

    There are no labels assigned to this post.