topic Re: How do I assign a SQL query result to a variable? in Discussions

How do I assign a SQL query result to a variable?

JaneCuthbertson — Thu, 20 Feb 2025 17:25:58 GMT

Hello! I am new to JMP/JSL scripting and am having some trouble figuring things out. I feel like what I am trying to do should be very simple but nothing seems to be working as I would expect and google is failing me.

What I am trying to do: I have a simple stored procedure that just returns a control limit value from our SQL database based on a few parameters I pass to it. I am then trying to assign that value to a Levey Jennings control limit for a column. The code below is as far as I have gotten.

This code is outputting a table with my desired value.

// Get limit value
LimitType = "Control";
LimitLevel = "Lower";
utagID = "6720";
vLCL = open database(
	"Driver={SQL Server};Server=FC-SQL-P\PROD;Database=ctc_config;Trusted_Connection=yes",
	"cus_sp_taglimits
		@LimitType = " || LimitType || ",
		@LimitLevel = " || LimitLevel || ",
		@utagid = '" || utagID || "'"
);

I am then trying to use the value of "vLCL" and apply it to a different tables column properties.

// Change column property
Data Table( "testTable" ):testColumn << Set Property(
	"Control Limits",
	{Levey Jennings( LCL( vLCL ) )}
);

The script above works fine when I replace "vLCL" with a number.

Where I think I am getting stuck is, how do I use my SQL result as an actual variable? Is there a way to query a value directly into a variable instead of pulling it into a new table?

Any help on this would be greatly appreciated! Thank you in advance!!

-Jane

Re: How do I assign a SQL query result to a variable?

jthi — Thu, 20 Feb 2025 17:36:29 GMT

If your vLCL has what you are looking for, you just need to evaluate it, here is one option on how to do it with column properties like this

Eval(EvalExpr(
	Data Table("testTable"):testColumn << Set Property(
		"Control Limits",
		{Levey Jennings(LCL(Expr(vLCL)))}
	);	
));

If you are unsure what is stored to vLCL you can for example add

show(vLCL);

after the query and verify it has what you are looking for

Re: How do I assign a SQL query result to a variable?

JaneCuthbertson — Thu, 20 Feb 2025 17:54:01 GMT

Thank you for the response! I tried editing it to what you shared and when I do a "Show" I am getting this:

vLCL = DataTable("SQL Results 799");

So I think my problem is that I am not actually assigning the result of the query to vLCL as a numeric value? Do you know how I can correct this?

Thanks again!!

Jane

Re: How do I assign a SQL query result to a variable?

jthi — Thu, 20 Feb 2025 17:57:19 GMT

Depends on your resulting table which vLCL holds. Data table subscripting might be enough, and sometimes you might have to combine it with << get rows where.

Below is very basic example of data table subscripting which takes values from first row from LSL and USL columns

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("LSL", Numeric, "Continuous", Format("Best", 12), Set Values([1])),
	New Column("USL", Numeric, "Continuous", Format("Best", 12), Set Values([2]))
);

my_lsl = dt[1, "LSL"];
my_usl = dt[1, "USL"];

Re: How do I assign a SQL query result to a variable?

JaneCuthbertson — Thu, 20 Feb 2025 18:11:22 GMT

Oh my gosh thank you this is exactly what I was missing!

I adjusted my code to the following and it is working perfectly!

// Get limit value -- returns a new table with a  single value
LimitType = "Control";
LimitLevel = "Lower";
utagID = "6720";
tblLCL = open database(
	"Driver={SQL Server};Server=FC-SQL-P\PROD;Database=ctc_config;Trusted_Connection=yes",
	"cus_sp_taglimits
		@LimitType = " || LimitType || ",
		@LimitLevel = " || LimitLevel || ",
		@utagid = '" || utagID || "'"
);
//assign table value to variable
vLCL = tblLCL[1,1];
// Change column property
Eval(EvalExpr(
	Data Table( "testTable" ):testColumn << Set Property(
		"Control Limits",
		{Levey Jennings( LCL( Expr(vLCL) ) )}
	);
));

Thank you thank you thank you!

-Jane