Passing user specified value to Open Database() SQL block

Bandfield

Good day,

 

  I currently have a block of SQL code being executed through an Open Database() command. This SQL block was generated by another person and I am trying to streamline the user interface. Currently an order number is manually entered in the SQL block and must be overwritten everytime a new order number is desired. I have built a JMP GUI that pulls a user specified order number.

 

 I would like to pass this user specified order number into the SQL code.

 

SQL segment where the current order number is manually entered (25891769)

where 
	ShopFloorControl.ManufacturingOrder.OrderNumber in (25891769)
	and ShopFloorControl.ManufacturingOrder.LineNumber = 0

 

 

Any assistance would be greatly appreciated.

 

Thank you

pmroz

You can use string concatenation to replace a variable in the SQL statement.  Here's one way to do it:

order_no = "25891769";
sql_statement = evalinsert(
"SELECT ...
   FROM ...
  WHERE  
	ShopFloorControl.ManufacturingOrder.OrderNumber in ('^order_no^')
	and ShopFloorControl.ManufacturingOrder.LineNumber = 0");

print(sql_statement);

Results in:
"SELECT ...
   FROM ...
  WHERE  
	ShopFloorControl.ManufacturingOrder.OrderNumber in ('25891769')
	and ShopFloorControl.ManufacturingOrder.LineNumber = 0"

If you have more than one order number you can use CONCAT ITEMS to create an IN list:

order_list    = {"25891769", "25891770", "25891771"};
order_inlist  = "('" || Concat Items(order_list, "', '") || "')";
sql_statement = evalinsert(
"SELECT ...
   FROM ...
  WHERE  
	ShopFloorControl.ManufacturingOrder.OrderNumber in ^order_inlist^
	and ShopFloorControl.ManufacturingOrder.LineNumber = 0");

print(sql_statement);

Results in:
"SELECT ...
   FROM ...
  WHERE  
	ShopFloorControl.ManufacturingOrder.OrderNumber in ('25891769', '25891770', '25891771')
	and ShopFloorControl.ManufacturingOrder.LineNumber = 0"