How to save results from SQL query in data table?

Question

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.

Ressel · Answer

That's weird. When I run a query, I cannot send messages directly to the table from the query. I need to first make that table into a data table. See example below. Am I misunderstanding something?

Table = New SQL Query(
	Version( 130 ),
	Connection( "ODBC:DSN=yournamehere;" ),
	QueryName( "MyQuery" ),
	Select(
		Distinct,
		Column( "A", "t1" ),
	),
	From(
		Table(
			"some table",
			Schema( "some view" ),
			Alias( "t1" )
		),
		Table(
			"another table",
			Schema( "another view" ),
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "BatchId", "t1" ), Column( "BatchId", "t2" ) )
			)
		)
	)
) << Run;

// does not work
Close( Table, no save );

// does work
NewTable = Data Table( Table );
Close( NewTable, no save );

Ah, I think I have a faint idea now what might have caused this problem:

Table = New SQL Query(
	Version( 130 ),
	Connection( "ODBC:DSN=yournamehere;" ),
	QueryName( "MyQuery" ),
	Select(
		Distinct,
		Column( "A", "t1" ),
	),
	From(
		Table(
			"some table",
			Schema( "some view" ),
			Alias( "t1" )
		),
		Table(
			"another table",
			Schema( "another view" ),
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "BatchId", "t1" ), Column( "BatchId", "t2" ) )
			)
		)
	)
) << Run Foreground; // this fixed it

// does work
Close( Table, no save );

lukasz · Answer

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.

Mark_Bailey · Answer

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.