Column value for SQL input

[#1]

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));

[#2]

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]
			)
		)
	)
);

[#3 · Accepted solution]

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);

[#4]

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^%'");

[#5]

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.

[#6]

Perfect.  Thank you so much!