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
ionatx
Level II

New SQL Query -- Join when one dt has variable # of columns

I have a script that joins four tables using the JMP Query Builder. A condensed example of the script is below. This works with a fixed number of columns, however dt1 can have any number of columns and I need to incorporate all of them. The remaining three tables have specific columns for inclusion, all other columns need to be dropped.

 

Names Default to Here( 1 );

dt1 = Open( "C:\...\dt1.jmp", invisible );
dt2 = Open( "C:\...\dt2.jmp", invisible );

name1 = dt1 << Get Name;
name2 = dt2 << Get Name;

list1 = dt1 << Get Column Names( String );
list2 = dt2 << Get Column Names( String );

New SQL Query(
	Query name( "dt3" ),
	Connection( "JMP" ),
	Select(
			Column( list1[1], "t1" ),
			Column( list1[2], "t1" ),
			Column( list1[3], "t1" ),
			Column( list2[2], "t2" ),
	),
	From(
		Table( name1, Alias( "t1" ) ),
		Table( name2, Alias( "t2" ),
			Join(
				Type( Inner ),
				GE(
					Column( list1[1], "t1" ), Column( list2[1], "t2" )
				)
			)
		)
	)
) << Run;

 

FOR loops result in "Syntax error in access or evaluation of 'For'".

	Select(
			For( i = 1, i <= N Items( list1 ), i++,
				Column( list1[i], "t1" ) ),
			Column( list2[2], "t2" ),
	),

I've tried wrapping various parts in EVAL(EVAL EXPR(EXPR())), but no dice.

 

Thoughts?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: New SQL Query -- Join when one dt has variable # of columns

Here is the brute force method that I use, when I can not figure out a slicker method to solve similar problems.  The code simply builds exactly the JSL it needs to run, and then executes it

Names Default to Here( 1 );

dt1 = Open( "C:\...\dt1.jmp", invisible );
dt2 = Open( "C:\...\dt2.jmp", invisible );

name1 = dt1 << Get Name;
name2 = dt2 << Get Name;

list1 = dt1 << Get Column Names( String );
list2 = dt2 << Get Column Names( String );

theExpr = "
New SQL Query(
	Query name( \!"dt3\!" ),
	Connection( \!"JMP\!" ),
	Select("
	
	For( i = 1, i <= N Items( list1 ), i++,
		theExpr = theExpr || "Column(" || list1[i] || ", \!"t1\!" ),";
	);
			
	theExpr = theExpr || "Column( list2[2], \!"t2\!" )
	),
	From(
		Table( name1, Alias( \!"t1\!" ) ),
		Table( name2, Alias( \!"t2\!" ),
			Join(
				Type( Inner ),
				GE(
					Column( " || list1[1] || ", \!"t1\!" ), Column( " || list2[1] || ", \!"t2\!" )
				)
			)
		)
	)
) << Run;";
eval(parse(theExpr));
Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: New SQL Query -- Join when one dt has variable # of columns

Here is the brute force method that I use, when I can not figure out a slicker method to solve similar problems.  The code simply builds exactly the JSL it needs to run, and then executes it

Names Default to Here( 1 );

dt1 = Open( "C:\...\dt1.jmp", invisible );
dt2 = Open( "C:\...\dt2.jmp", invisible );

name1 = dt1 << Get Name;
name2 = dt2 << Get Name;

list1 = dt1 << Get Column Names( String );
list2 = dt2 << Get Column Names( String );

theExpr = "
New SQL Query(
	Query name( \!"dt3\!" ),
	Connection( \!"JMP\!" ),
	Select("
	
	For( i = 1, i <= N Items( list1 ), i++,
		theExpr = theExpr || "Column(" || list1[i] || ", \!"t1\!" ),";
	);
			
	theExpr = theExpr || "Column( list2[2], \!"t2\!" )
	),
	From(
		Table( name1, Alias( \!"t1\!" ) ),
		Table( name2, Alias( \!"t2\!" ),
			Join(
				Type( Inner ),
				GE(
					Column( " || list1[1] || ", \!"t1\!" ), Column( " || list2[1] || ", \!"t2\!" )
				)
			)
		)
	)
) << Run;";
eval(parse(theExpr));
Jim
ionatx
Level II

Re: New SQL Query -- Join when one dt has variable # of columns

Thanks @txnelson, that is exactly as circuitous as I expected it would be. In the interest of script maintenance (i.e. I don't know what I'm doing and will invariably forget) I might just create a set of default columns that will be incorporated regardless of what data is in them. In anyone thinks of another method, please post!