The World Statistics Day celebration continues here in the Community. We all need reliable data for sound decision making. Do you have a data source that you trust most? Head over to Discussions to tell us about it.
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
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!
Article Labels

    There are no labels assigned to this post.