Subscribe Bookmark RSS Feed

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

jpol

Community Trekker

Joined:

Oct 11, 2015

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

1 ACCEPTED SOLUTION

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

20 REPLIES
erichill

Staff

Joined:

Oct 1, 2013

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

jpol

Community Trekker

Joined:

Oct 11, 2015

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

erichill

Staff

Joined:

Oct 1, 2013

Hey, Philip,

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

Eric

ron_horne

Super User

Joined:

Jun 23, 2011

Hi jpol​,

Perhaps this example can be useful:

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

ron

erichill

Staff

Joined:

Oct 1, 2013

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

jpol

Community Trekker

Joined:

Oct 11, 2015

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

pmroz

Super User

Joined:

Jun 23, 2011

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.

jpol

Community Trekker

Joined:

Oct 11, 2015

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

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

-Jeff