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

[#1]

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

[#2]

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

[#3]

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".

[#4]

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.

[#5]

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

[#6]

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.

[#7 · Accepted solution]

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

[#8]

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

[#9]

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

);

[#10]

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

[#11]

Hi

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