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

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

ionatx — Sat, 05 Oct 2019 06:35:00 GMT

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?

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

txnelson — Sat, 05 Oct 2019 20:54:17 GMT

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

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

ionatx — Fri, 18 Oct 2019 20:09:15 GMT

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!