Choose Language Hide Translation Bar
Highlighted
jpol
Level IV

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

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

Highlighted
jpol
Level IV

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

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

Highlighted
msharp
Super User

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

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.

Highlighted
jpol
Level IV

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

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

Highlighted
msharp
Super User

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

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.

Highlighted
pmroz
Super User

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

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

View solution in original post

Highlighted
jpol
Level IV

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

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

Highlighted
msharp
Super User

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

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

);



Highlighted
jpol
Level IV

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

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

Highlighted
guy_yosef
Level III

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

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

Article Labels

    There are no labels assigned to this post.