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]

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

[#3]

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

[#4]

Hey, Philip,

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

Eric

[#5]

Hi jpol​,

Perhaps this example can be useful:

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

ron

[#6]

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

[#7]

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

[#8]

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.

[#9]

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

[#10]

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