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
lukasz
Level III

How to save results from SQL query in data table?

Hello Everybody,

I have the following issue. I want to execute SQL query to get data from database and that is working well. After that I would to save data from query in data table and use that for further processing. Based on ideas from other posts I did a simple assignment to the new variable dt_TestData. I am providing as argument for Data Table the name taken from QueryName. However an error occurs when I want to save data in the new variable. The execution of query works well and the new table with name Experimental_Data opens automatically, but I would like to make the reference to that table. I am overlooking something and not able to notice that. I would appreciate for help how solve this issue. I am using JMP15. One more question, how to disable displaying the table after query execution? Best regards.

Data = New SQL Query(
	Connection(
		"ODBC:Description=Datenbank;DRIVER=SQL Server;SERVER=Server;UID=%_UID_%;PWD=%_PWD_%;APP=JMP;DATABASE=EngineeringSupport;"
	),
	QueryName( "Experimental_Data" ),
	Select(
		Column( "Param1", "t1"),
		Column( "Param2", "t1"),
		Column( "Param3", "t1"),
	),
	From( Table( "TestData", Schema( "dbo" ), Alias( "t1" ))),
) << Run;

dt_TestData = Data Table("Experimental_Data");
Open(dt_TestData);
//further actions with dt_TestData: slicing, joining with other tables, etc.

 

4 REPLIES 4
Highlighted
gzmorgan0
Super User

Re: How to save results from SQL query in data table?

@lukasz ,

 

The last JSL  statement  is the issue: Open( dt_testData ).  If the query ran successfully, the data table is already opened. If you want to make another copy of that table, use Subset. In teh example below dt2 is different table.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = dt << Subset( All Rows, Selected Columns(0), Output Table Name("Big2") );
Highlighted
lukasz
Level III

Re: How to save results from SQL query in data table?

gzmorgan0,
Thank you for reply! I should have explained that more accurately, the problem is I could not create "dt" variable which would make reference to the data resulted from the query. After the query is executed the new table is created an opened but where can I find (or create) the variable referencing the data? Or it is somehow created and I cannot see/use that? JMP is showing error regarding this line:
dt_TestData = Data Table("Experimental_Data");
"The data table cannot be found when accessing or evaluating Data Table,  Data Table/*###*/("Experimental_Data")"
I think the variable dt_TestData cannot be created in such a way? I would appreciate for further help to solve the issue.

 

 

Highlighted

Re: How to save results from SQL query in data table?

The variable Data stores the reference to the data table returned from the query. The function call Data Table( "Experimental Data' ) returns a reference to the open data table Experimental Data. You can send messages to the reference stored in Data.

Learn it once, use it forever!
Highlighted
lukasz
Level III

Re: How to save results from SQL query in data table?

markbailey, thank you for reply. I did not know how to get the reference by scripting but after additional searching I found the solution in this post. Thank you all for your time and best regards.

Article Labels

    There are no labels assigned to this post.