cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
smithwoosley
Level II

Column value for SQL input

I feel like this should be simple, but after trying various methods over the past few days, searching previous discussions, and referencing the scripting index, I've come up empty.

 

I want to loop through the values from the data table and use them as inputs for the SQL query, where the data table values are "lot codes" and the SQL query references "where vcLotCode like...".  I'll deal with the looping later, I'm struggling to get even the first value to work.  I've tried various iterations of "select", "set", "get", etc....  Any help is appreciated.

 

// Data table

dt=New Table ("Lots",
	Add Rows(5),
	New Column ("vcLotCode",
		Character,
		Nominal,
		Set Values ({"lcode.0123","12345.4321","dcode.0123","54321.0123","lotcd.4567"})
	),
);

// One attempt to set lot code to values from table

dt<<Select Rows(1);
lotcode = :vcLotCode <<Get Selected;

// Attempting to pull data from database using lot code as lookup variable

DSNString = "Driver={SQL Server}; SERVER=rpt; DATABASE=FAB; UID=rrpt; ";

sqlStr=
"
select vcLotCode, vcWaferCode, dAvg
from fr.database
where vcLotCode like '"||lotcode||"'
";

dt2 = Eval(Substitute(Expr(dt=Open Database(DataSource,sqlcode,"Data");),Expr(sqlcode),sqlstr,Expr(DataSource),DSNString));
1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Column value for SQL input

A few things:

o You are using Open Database.  I prefer create database connection + execute SQL + close database connection.  The advantage of execute SQL is that you create one connection to the database and can execute multiple SQL statements with it.  Open database creates a new connection for every SQL statement

o You don't need to use all of those evals, substitute etc.  In addition there are variables that are not defined like datasource and sqlcode.

 

Try this code:

// Data table
dt = New Table( "Lots",
	Add Rows( 5 ),
	New Column( "vcLotCode",
		Character,
		Nominal,
		Set Values( {"lcode.0123", "12345.4321", "dcode.0123", "54321.0123", "lotcd.4567"} )
	),
);

// Pull data from database using lot code as lookup variable
DSNString = "Driver={SQL Server}; SERVER=rpt; DATABASE=FAB; UID=rrpt; ";

// Create a connection to SQL Server
dbc = create database connection(dsnstring);

// Loop over all lot numbers
for (i = 1, i <= nrows(dt), i++,
	lotcode = dt:vclotcode[i];
	sqlStr = evalinsert(
"select vcLotCode, vcWaferCode, dAvg
   from fr.database
  where vcLotCode like '^lotcode^'");
	print(sqlstr);

// Create a separate table for each lot number
	dt2 = execute sql(dbc, sqlstr);
);
close database connection(dbc);

View solution in original post

5 REPLIES 5

Re: Column value for SQL input

There are two points to consider. First, the code can be greatly simplified. This change will reduce the opportunity for errors and make them easier to find. Second, you are manipulating character strings for the arguments to Open Database() function. The double quotation mark is the delimiter for strings. Embedded double quotes must therefore be identified with the escape sequence "\!" (back slash, bang).

 

Test this script. (I can't.)

 

dt = New Table( "Lots",
	Add Rows( 5 ),
	New Column( "vcLotCode", Character, Nominal, Set Values( {"lcode.0123", "12345.4321", "dcode.0123", "54321.0123", "lotcd.4567"} ) )

);

For( r = 1, r <= N Row( dt ), r++,
	Eval(
		Parse(
			Substitute(
				"dt2 = Open Database(
					\!"Driver={SQL Server}; SERVER=rpt; DATABASE=FAB; UID=rrpt; \!",
					\!"select vcLotCode, vcWaferCode, dAvg from fr.database where vcLotCode like '\!"LLLLL\!"'\!",
					\!"Data\!"
				)",
				"LLLLL", dt:vcLotCode[r]
			)
		)
	)
);
pmroz
Super User

Re: Column value for SQL input

A few things:

o You are using Open Database.  I prefer create database connection + execute SQL + close database connection.  The advantage of execute SQL is that you create one connection to the database and can execute multiple SQL statements with it.  Open database creates a new connection for every SQL statement

o You don't need to use all of those evals, substitute etc.  In addition there are variables that are not defined like datasource and sqlcode.

 

Try this code:

// Data table
dt = New Table( "Lots",
	Add Rows( 5 ),
	New Column( "vcLotCode",
		Character,
		Nominal,
		Set Values( {"lcode.0123", "12345.4321", "dcode.0123", "54321.0123", "lotcd.4567"} )
	),
);

// Pull data from database using lot code as lookup variable
DSNString = "Driver={SQL Server}; SERVER=rpt; DATABASE=FAB; UID=rrpt; ";

// Create a connection to SQL Server
dbc = create database connection(dsnstring);

// Loop over all lot numbers
for (i = 1, i <= nrows(dt), i++,
	lotcode = dt:vclotcode[i];
	sqlStr = evalinsert(
"select vcLotCode, vcWaferCode, dAvg
   from fr.database
  where vcLotCode like '^lotcode^'");
	print(sqlstr);

// Create a separate table for each lot number
	dt2 = execute sql(dbc, sqlstr);
);
close database connection(dbc);
pmroz
Super User

Re: Column value for SQL input

One more comment - you are using the LIKE operator but not using wildcards.  Perhaps you meant something like this?

"select vcLotCode, vcWaferCode, dAvg
   from fr.database
  where vcLotCode like '%^lotcode^%'");

Re: Column value for SQL input

I agree that in this case, there is an advantage to breaking the three steps out into separate actions as you are interatiing over the queries.

 

Using this approach, you could also just use Substitute() on the boilerplate sqlstr.

smithwoosley
Level II

Re: Column value for SQL input

Perfect.  Thank you so much!