Script - Work with Data Table from OBBC query

[#1]

Hi Everyone -

I am working with two datasets stored in Microsoft Access database, which I get through ODCB queries. I need to join the two resulting data tables, before I make some more data manipulations.

I have scripted the whole process, however it doesn't not run fully at once. I always have to first run the script for the two ODCB queries, then run my script for tables joining and other manipulations.

Else I get an error message: "cannot locate data table in access or evaluation of 'data table' ,  Data Table/*###*/("qStreams")"

("qStreams" being of the two tables I got from ODCB query).

I feel I may need to add some script to store/declare the data tables I get from the ODCB queries in order to use it later, and this is where I am stuck, because I am very unfamiliar with JSL script (this is my first script actually).

Thanks for your help.

[#2]

Hi @JRS,

Try retaining a reference to the data tables you open with each ODBC connection and using those instead of calling by name, like this:

dt1 = Open Database( "DSN=My Data Source Name", "SELECT * FROM table1" );
dt2 = Open Database( "DSN=My Data Source Name", "SELECT * FROM table2" );

dt1 << Join(
	With( dt2 ),
	...
);

[#3]

Many thanks for the quick reply.

I get the concept but not able to apply it for my specific case. My query script looks like this right now:

New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DBQ=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb;DefaultDir=C:\Users\E822710\Desktop\ROTF\Database For JMP;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
	),
	QueryName( "qProperties" ),
	Select(
		Column( "CaseID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamTag", "t1" ),
		Column( "StreamDescription", "t1" ),
		Column( "QualityID", "t1", Analysis Type( "Nominal" ) ),
		Column( "QualityTag", "t1" ),
		Column( "QualityDescription", "t1" ),
		Column( "Value", "t1", Alias( "StreamQualityValue" ) ),
		Column( "OriginDescription", "t1", Alias( "StreamOriginDescription" ) )
	),
	From( Table( "RW_StreamQualities", Alias( "t1" ) ) ),
	Where(
		In List(
			Column( "QualityTag", "t1" ),
			{"CRB"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	),
	Order By(
		Column( "CaseID", "t1", Order( "Ascending" ) ),
		Column( "StreamReportOrder", "t1", Order( "Ascending" ) ),
		Column( "QualityReportOrder", "t1", Order( "Ascending" ) )
	)
) << Run;

I realize I should have just shared my script right away.

Appreciate your further guidance.

[#4]

You would set a variable equal to the value of New SQL Query.  Using the highest level functions of your script, it might look like this:

//Get data
dtqStreams = New SQL Query( ... ) << Run;
dtqProperties = New SQL Query( ... ) << Run;

// Join both Data Tables
dtJoined = dtqStreams << Join( With( dtqProperties ), ... );

// Set column properties
dtJoined:"StreamFeedFlag"n << Add Column Properties( ... );

// Add new columns
dtJoined << New Column( ... );

// Make graphs from your joined data table
dtJoined << Graph Builder( ... );

[#5]

Hi -

Still having some issue around joining both tables, here is the script I implemented following your post:

// Join both Data Tables
dtJoined = dtqStreams << Join(
	With( dtqProperties ),
	Merge Same Name Columns,
	Match Flag( 0 ),
	By Matching Columns( :CaseID = :CaseID, :StreamID = :StreamID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "UnitBalance.HY%" )
);

But it still gives an error:

[#6]

So here I have to admit that I don't use New SQL Query very often.  Your code looks correct.  Two ideas here, one that you might need to put parentheses around (New SQL Query( ... ) << Run), and another is to include a wait statement to make sure the queries actually evaluate before moving forward.  Try running your code one line at a time, and see if the n row functions returns the number you expect.

//Get data
dtqStreams = (New SQL Query( ... ) << Run);
dtqProperties = (New SQL Query( ... ) << Run);

//Make sure everything evaluates
wait(1);

//check that table contains values
n row( dtqStreams );

// Join both Data Tables
dtJoined = dtqStreams << Join( With( dtqProperties ), ... );

// Set column properties
dtJoined:"StreamFeedFlag"n << Add Column Properties( ... );

// Add new columns
dtJoined << New Column( ... );

// Make graphs from your joined data table
dtJoined << Graph Builder( ... );

[#7]

Thanks for taking the time to look into it. Unfortunately that did not work.

I have reached out to the JMP support, but have heard back from them till now. Not sure if they address this kind of technical query.

[#8 · Accepted solution]

Are you running the query in foreground or in the background (I think this is preference dependant but you can force it)? If they are run in the background it could mean that the queries do not finish before you try to join datatables together.

[#9]

@jthi good thought!

If this is the issue, this post might help: Run queries in background, wait for them to complete, and reference them in the remaining JSL script 

[#10]

JMP support guys did respond but it was in my junk box.

They suggested just as you mentioned, and it worked just fine.

Pasting their detailed feedback for everyone's benefit:

 ================================================================================================

Sending the <<Run message to New SQL Query() causes JMP to use the Query Builder preference for running queries. Unless you have updated your preferences, SQL Queries are run in the background by default.

<![if !vml]>

<![endif]>

Use <<Run Foreground with the OnRunComplete option to assign a reference to the imported table.  This will cause your script to wait for the query to complete.  Additionally, Query Builder creates a symbol that is only available to the OnRunComplete() script called 'queryResult.' This symbol is a reference to the resulting table.

 << Run Foreground( On Run Complete( dt = queryResult ) );

You can read more about the available functions and their arguments the the SQL Functions section of the JSL Syntax Reference.

================================================================================================

So in application:

dtqProperties = (New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DBQ=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb;DefaultDir=C:\Users\E822710\Desktop\ROTF\Database For JMP;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\E822710\Desktop\ROTF\Database For JMP\Results.Mdb.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
	),
	QueryName( "qProperties" ),
	Select(
		Column( "CaseID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamID", "t1", Analysis Type( "Nominal" ) ),
		Column( "StreamTag", "t1" ),
		Column( "StreamDescription", "t1" ),
		Column( "QualityID", "t1", Analysis Type( "Nominal" ) ),
		Column( "QualityTag", "t1" ),
		Column( "QualityDescription", "t1" ),
		Column( "Value", "t1", Alias( "StreamQualityValue" ) ),
		Column( "OriginDescription", "t1", Alias( "StreamOriginDescription" ) )
	),
	From( Table( "RW_StreamQualities", Alias( "t1" ) ) ),
	Where(
		In List(
			Column( "QualityTag", "t1" ),
			{"CRB"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	),
	Order By(
		Column( "CaseID", "t1", Order( "Ascending" ) ),
		Column( "StreamReportOrder", "t1", Order( "Ascending" ) ),
		Column( "QualityReportOrder", "t1", Order( "Ascending" ) )
	)
) << Run Foreground( On Run Complete( dt = queryResult ) ));