topic Re: How to insert a loop into SQL which is used in a JSL script? in Discussions

How to insert a loop into SQL which is used in a JSL script?

jpol — Thu, 17 Dec 2015 11:51:29 GMT

Hi,

I have a need to collect chip-level parametric test data from final testers for many productionwafers.

Each wafer has ~ 3000 chips and 10s of parameters.

I have created a dummy SQL for the purpose of this request for assistance:

SELECT

I.PRODUCTNR,

T.TESTUNITNR,

T.MEASUREMENTPOINTNR,

T.CREATIONTIME,

T.RESULT_PARAM1,

T.RESULT_PARAM2.

FROM

"ITEMS" D,

"TESTRESULTS" T,

"TESTRESULTROWS" TRR,

"TESTROW" TR,

WHERE I.IDNR = T.IDNR

AND T.TESTRESULTNR = TRR.TESTRESULTNR

AND TRR.TESTROWIDNR = TR.TESTROWIDNR

AND I.LOTIDNR = L.LOTIDNR

AND T.VALIDITY = '1'

AND D.WAFERNR IN

(

'WAF43K309A',

'WAF43K310A',

'WAF43K311A',

'WAF43K312A',

'WAF43K313A',

'WAF43K314A',

., ., ., ., .,

'WAF43K315A',

'WAF43K316A',

'WAF43K317A',

'WAF43K319A',

'WAF43K320A',

'WAF43K321A'

)

AND (T.TESTUNITNR LIKE ('HENE%')

AND TR.VARIABLENAME IN

( 'ACENUM',

'OFFSET_X_DIFF',

'OFFSET_Y_DIFF',

......)

GROUP BY ...

If I collect all data for the first wafer listed ( WAF43K309A), the collection / fetch time is ~ 90 seconds.

If I collect all data for the first 2 wafers listed (WAF43K309A,WAF43K310A), the collection / fetch time is ~ 4 minutes

If I collect all data for the first 3 wafer listed (WAF43K309A,WAF43K310A,WAF43K311A), the collection / fetch time is ~ 7 minutes

I am sure you see the problem, the collect / fetch time grows exponentially with the number of wafers.

Sometime I have to do this over the weekend!!

Using the SQL "Union All" operator and collecting data for one wafer at a time, I can retrieve the data using approximately the same amount of time for each wafer.

This is ok if I have only a few wafers in my list.Unfortunately, I quite often need to collect data from several 10s of wafers , even hundreds of wafers.

Using an inner select I could make a listing of the wafers:

e.g.

.....

AND D.WAFERNR IN (

SELECT WAFERNR from ITEM

WHERE TESTDATE > SYSDATE-5)

the resulting wafer list would be:

WAF43K309A

WAF43K310A

WAF43K311A

WAF43K312A

WAF43K313A

WAF43K314A

WAF43K315A

WAF43K316A

WAF43K317A

WAF43K319A

WAF43K320A

WAF43K321A

Is it possible, using JSL, to use this listing by looping thru the list and collecting data for one wafer at a time?

Looking forward to seeing what creative solutions the community may have to offer

A solution would be a great Christmas present

Best Regards,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

Eric_Hill — Thu, 17 Dec 2015 13:34:25 GMT

Hey, Philip,

Looping is certainly possible, but first things first: As written, your SQL statement generates a Cartesian product, which could account for the exponential size increase. Is that necessary?

Eric

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Thu, 17 Dec 2015 14:01:36 GMT

Hi Eric.

Thank you for your concern.

As stated, this is a dummy Sql which is much shorter than the original.

I was hoping to just present the case from a JSL perspective rather than the SQL.

Rgds

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

Eric_Hill — Thu, 17 Dec 2015 14:07:49 GMT

Hey, Philip,

Okay, sorry, just wanted to make sure. I will work up an example of looping.

Eric

Re: How to insert a loop into SQL which is used in a JSL script?

ron_horne — Thu, 17 Dec 2015 14:18:16 GMT

Hi jpol,

Perhaps this example can be useful:

https://community.jmp.com/message/223900#223900

ron

Re: How to insert a loop into SQL which is used in a JSL script?

Eric_Hill — Thu, 17 Dec 2015 14:39:40 GMT

Thanks for the pointer, Ron, that saved me some time. So using the example Ron pointed to, I put the wafer ID's of interest into a data table named wafers.jmp, and then wrote this script:

sqlTemplate = "\[

SELECT

I.PRODUCTNR,

T.TESTUNITNR,

T.MEASUREMENTPOINTNR,

T.CREATIONTIME,

T.RESULT_PARAM1,

T.RESULT_PARAM2

FROM

"ITEMS" D,

"TESTRESULTS" T,

"TESTRESULTROWS" TRR,

"TESTROW" TR,

WHERE I.IDNR = T.IDNR

AND T.TESTRESULTNR = TRR.TESTRESULTNR

AND TRR.TESTROWIDNR = TR.TESTROWIDNR

AND I.LOTIDNR = L.LOTIDNR

AND T.VALIDITY = '1'

AND D.WAFERNR = '^wafer_id^'

AND (T.TESTUNITNR LIKE ('HENE%')

AND TR.VARIABLENAME IN

( 'ACENUM',

'OFFSET_X_DIFF',

'OFFSET_Y_DIFF' )

;

]\";

open( "wafers.jmp" );

for each row(

wafer_id = :WaferID;

sql = Eval Insert( sqlTemplate );

Print( sql );

);

That just prints out the munged SQL of course; you can add code to submit the SQL to your database.

HTH,

Eric

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Fri, 18 Dec 2015 10:07:32 GMT

Thanks Ron for the link.

As a newcomer to JMP and scripting I do not yet understand the logic completely but will try to figure it out.

Best Regards,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Fri, 18 Dec 2015 10:18:51 GMT

Hi Eric,

Thank you for the script which I have tried to modify for the original SQL.

The long SQL string is being created from the wafer list in wafers.jmp but for some reason or other I am only getting results from one wafer.

Even if I duplicate the one wafer ID several times, I still get only one wafer's worth or test results.

Hopefully my understanding is correct...

I first set up the template, as per your example....

Then I set up the default directory where "wafers.jmp" is stored .....

Set the loop going.....

for each row(

wafer_id = :WaferID;

sql = Eval Insert( sqlTemplate );

Print( sql );

);

Connect to the database and run the sql ???

Open Database(
"DSN=TESTING;UID=tepo;PWD=password;DBQ=Production;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;TSZ=8192;",
Eval Insert( sql)

);
;

It's this last part that I am not sure of.

Any ideas, suggestions.

It seem that we are quite close....

Best Regards,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

Jeff_Perkinson — Fri, 18 Dec 2015 14:57:18 GMT

I'd also suggest, respectfully, that you make sure that your SQL is optimized appropriately.

As EricHill points out a query without a JOIN usually results in a cartesian product matching every row of every table with every row of every other table. This can be very time and resource intensive. The example code you provided doesn't have a JOIN. If that is representative of your actual code it may be worth investigating whether you can improve the SQL as well as the JSL.

-Jeff

Re: How to insert a loop into SQL which is used in a JSL script?

pmroz — Fri, 18 Dec 2015 15:02:29 GMT

If you are running many SQL statements in a loop you should take a different approach:

dbc = open database connection("<your-connection-string-goes-here>");

for each row(

sql = ...

execute sql(dbc, sql);

);

close database connection(dbc);

This way you only connect to the database once, which can save you a bit of time.

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Fri, 18 Dec 2015 18:18:13 GMT

Thank you Jeff for your good advice.

This particular data acquisition is indeed quite challenging and requires careful planning as data are retrieved from many DB tables containing many TB of data.

3 of our own DB administrators as well as 2 external companies have worked on its optimization.

Hence the reason for seeking this " one wafer at a time" approach .

This approach was recently demonstrated successfully, using the same SQL by Denodo but at the moment we do not have Denodo s/w yet available.

Best regards,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Tue, 22 Dec 2015 11:31:30 GMT

Hi Eric, Ron and PMroz,

Thank you all for your input.

Unfortunately my JSL competence is not at a level that I can take all your inputs and build a working script.

I have not been able to ascertain the logic and sequence from your 3 inputs. I am still confused, but at a higher level

My efforts are returning data only for the last wafer in my "wafers.jmp" list.

How about I take a much easier example so that, at least I do not get confused.

I have a list of wafers in a file named "wafers.jmp":

XC6A49L410.1

XC6A49L411.1

XC6A49L412.1

XC6A49L413.1

XC6A49L414.1

XC6A49L415.1

XC6A49L416.1

XC6A49L417.1

XC6A49L418.1

I need to collect the WIPSTATUS of the listed wafers using the appropriate SQL:

SELECT WaferNR,WIPSTATUS

FROM "DISCSET"

WHERE WaferNR = ( all wafers in the list above )

In order to do this I need to set up the connection string:

"DSN=MEASdata;UID=USER;PWD=PASSWORD;DBQ=MDMANU.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;TSZ=8192;"

I need to establish the variable "wafer_id" '^WAFER_ID^' .........

set up a loop to build the required SQL that will collect the required data, one wafer at a time ........

open the database connection...

run the "munged" SQL ........

collect all data into one data table. ........

disconnect from the database ??

Using the simple example above could you guys please assist in constructing a script which could be tested so that I can attempt to work on the more advanced case myself.

Best Regards,

Philip

PS Please note that this is just a simple example to what I want to do, This data acquisition takes milliseconds while the advanced case currently takes from many hours to possibly days as I do not have the possibility to pull data for "one wafer at a time".

Re: How to insert a loop into SQL which is used in a JSL script?

msharp — Tue, 22 Dec 2015 19:00:22 GMT

You got it, go do it.

I think you have your approach wrong. If you need the jmp community to hand feed you the code for the simple case, there is no way you'll be able to implement it for the complicated case. It seems like you've taken on a project way over your head. In which case, I'd recommend you read the Scripting Guide or take a class.

Really, to get the most from a community you should first develop a code yourself; then when stuck during debugging or optimization the community can better help you refactor it. Asking for code straight up like this is little more than plagiarism.

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Tue, 22 Dec 2015 19:38:57 GMT

My apologies MSHARP if I have offended you in some way or other with my direct request for assistance.

As I myself have already stated, unfortunately I am not an expert in JSL but I have managed to do quite alot based on the contents offered by this community.

I did in fact attend a 2 day JMP JSL class but unfortunately due to its being organised by a local office in Europe for an American teacher with a very international class there were many technical glitches and a lot of time was lost over the 2 days, leaving a good deal of the material and workouts not being covered.

I have had good feedback so far from other members of this community and am in fact working with JMP on a forthcoming tutorial based on an area where I have more knowledge and experience.

On no occasion have I claimed any of the assistance offered by fellow members as my own work. I have been open in stating where the assistance is coming from and how great it feels to belong to such a supportive group where it is possible to climb the learning curve faster and further than in other communities.

I can only hope that others share my sentiment and continue to support and teach the less capable.

Sincerely,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

msharp — Tue, 22 Dec 2015 20:42:43 GMT

You are clearly missing the message AND intent of what I said.

Anyways, there's no reason this forum post needed be longer than Ron Horne's post since he links to a forum where I already solved this problem step by step.

Re: How to insert a loop into SQL which is used in a JSL script?

pmroz — Tue, 22 Dec 2015 23:41:46 GMT

I think this will do the trick:

// Example table - you don't have to do this step if the table already exists. But you should create

// a variable to point to it.

wafer_dt = New Table( "Wafer Numbers",

Add Rows( 9 ),

New Column( "Wafer Number",

Character,

Nominal,

Set Values(

{"XC6A49L410.1", "XC6A49L411.1", "XC6A49L412.1", "XC6A49L413.1",

"XC6A49L414.1", "XC6A49L415.1", "XC6A49L416.1", "XC6A49L417.1",

"XC6A49L418.1"}

)

);

// For example if your table is called Wafer Numbers this statement will assign a variable to that table

// wafer_dt = data table("Wafer Numbers");

// Store the wafer numbers in a list

wafer_list = column(wafer_dt, "Wafer Number") << get values;

dsn_string = "DSN=MEASdata;UID=USER;PWD=PASSWORD;DBQ=MDMANU.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;TSZ=8192;";

// Create a SQL statement where the wafer number is a variable to be substituted in during the loop

sql_statement = evalinsert(

"\[ SELECT WaferNR,WIPSTATUS

FROM "DISCSET"

WHERE WaferNR = '^one_wafer^']\");

// Create a connection to the database

dbc = create database connection(dsn_string);

for (i = 1, i <= nitems(wafer_list), i++,

one_wafer = wafer_list[i];

// Name the table the same as the wafer. Change to suit your needs

tbl_name = one_wafer;

// Retrieve the data for one wafer

dt = execute sql(dbc, sql_statement, tbl_name);

);

// Now that we're done retrieving all of the wafers into separate tables

// close the connection to the database

close database connection(dbc);

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Wed, 23 Dec 2015 10:07:17 GMT

Hi PMroz,

Many thanks for your support and your patience.

Based on your input, as well as that of EricHill, Ron Horne and msharp I am now able to set up the nested SQL query as I had hoped.

This method is directly transferable to the more advance data acquisition task mentioned at the beginning of this post and it will save many hours of waiting for data to be returned from the server.

I have learned a lot in this exercise and appreciate all your efforts.

One last question....

Now that I have all the data collected into separate data tables ( done to speed up data acquisition) is there a way, without having to save all the data tables, to perform a concatenation on all open tables so that I can more easily perform data analyses?

I can do this already by saving all data tables and using JSL to concatenate them on re opening, but it seems silly to have to do it this way.

Wishing you all a Happy Christmas,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

msharp — Wed, 23 Dec 2015 15:34:17 GMT

There are many ways to go about this last question. Probably the most straight forward and easiest solution isn't very intuitive. What you want to do is create a table list and then push each data table to the list. Then you can concatenate the list together at once. Here's the outline:

myTables = {};

For(i=1...

dt = execute sql(...);

InsertInto(myTables,dt);

);

dtAll = New Table("All Data");

dtAll << Concatenate(

eval(myTables),

Output Table( "All Data" )

);

Re: How to insert a loop into SQL which is used in a JSL script?

jpol — Mon, 28 Dec 2015 12:15:08 GMT

Hi msharp,

Many thanks for your input.

I have now combined this "vertical joining" section of the script section into the main script and it works a treat

Time required for data extraction has reduced to a fraction of what it was earlier and the possibility to script and automate the whole procedure allows me do this during the night when our servers are under a lighter load.

Best Regards,

Philip

Re: How to insert a loop into SQL which is used in a JSL script?

guy_yosef — Mon, 04 Jan 2016 15:16:12 GMT

i use the following script to create a line that contain a lot list

lot_list = Column(1) << Get values;

in_list = "('" || Concat Items( lot_list, "','" ) || "')";

sql_string = "WHERE \!"LOT\!" IN " || in_list;

/// If you want to see the string ///

show (sql_string);

*if lot number have a space some place and you need to search for "like" '%lot number'... it doesn't work in SQL. you will need to create different script