cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
varshini
Level I

how to query an external data base to the existing data table in JMP

This is the code that currently works , but i would like to automate it and probably hard code the section in BOLD Italics below - which is the list of CMOS_Lot-Wafer from the  "Wafer List" datatable, I have also attachged the code that i tried to hard code this section and does not work. Could someone take a look at it and help me out please, Thanks 

 

(Wafer list is the user list that the user will provide) 

 

dt1 = Data Table( "Wafer List" ) << Stack(
	columns( :Name( "Group A - Wafer Number" ), :Name( "Group B - Wafer Number" ) ),
	Source Label Column( "Label" ),
	Stacked Data Column( "CMOS-Lot-Wafer" ),
	Output Table( "Stacked-waferList" )
);

dt3 = Open Database(
	"DSN=MS_SQL;Description=WAFERVIEWER;Trusted_Connection=Yes;APP=JMP;WSID=USSJC-J9K98H2;DATABASE=PRODUCT;",
	"SELECT T.[CHECK AXSELFTEST], T.[CMOS_Lot-WaferID] FROM Summary S
INNER JOIN WS_Tests T ON T.[CMOS_Lot-WaferID] = S.[CMOS_Lot-WaferID] AND T.RetestID = S.RetestID
WHERE S.[CMOS_Lot-Wafer] IN ('DBM301-02',
'DBM308-12',
'DBM306-24',
'DBM297-03',
'DBM308-20',
'DBM297-02',
'DBM316-03',
'DBM306-09',
'DBM024-09'
)",
	"Summary"
);

dt2 = Data Table( "Stacked waferList" ) << Join(
	With( Data Table( "Summary" ) ),
	Select( :Label, :Name( "CMOS-Lot-Wafer" ), :Name( "CMOS_Lot-WaferID" ) ),
	SelectWith( :DieID, :CHECK AXSELFTEST ),
	By Matching Columns( :Name( "CMOS_Lot-WaferID" ) = :Name( "CMOS_Lot-WaferID" ) ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ), 
	Output Table( "WS- Summary for WaferList" )
);

 

//Code does NOT work -  I tried to hard code the CMOS_Lot-Wafer part but does not work

 

(Wafer list is the user list that the user will provide) 

 

dt1 = Data Table( "Wafer List" ) << Stack(
	columns( :Name( "Group A - Wafer Number" ), :Name( "Group B - Wafer Number" ) ),
	Source Label Column( "Label" ),
	Stacked Data Column( "CMOS-Lot-Wafer" ),
	Output Table( "Stacked-waferList" )
);

SQLConnection = Create Database Connection(
	"DSN=MS_SQL;Description=WAFERVIEWER;Trusted_Connection=Yes;APP=JMP;WSID=USSJC-J9K98H2;DATABASE=ISTANBUL;"
);
PrePUSQL01 =
"SELECT
T.CHECK AXSELFTEST,
C.CMOS_Lot-Wafer
FROM database.dbo.WS_tests AS T
JOIN Stacked waferList AS C ON C.CMOS_Lot-Wafer = T.CMOS_Lot-Wafer";

dt = Execute SQL( SQLConnection, PrePUSQL01, "dt" );
2 REPLIES 2
pmroz
Super User

Re: how to query an external data base to the existing data table in JMP

This doesn't look like valid SQL code:

"SELECT
T.CHECK AXSELFTEST,
C.CMOS_Lot-Wafer
FROM database.dbo.WS_tests AS T
JOIN Stacked waferList AS C ON C.CMOS_Lot-Wafer = T.CMOS_Lot-Wafer";

It appears that you are trying to combine SQL with JSL, which is not possible.  Does the SQL statement run in a database interface package like TOAD, PL/SQL Developer, SQL Navigator etc.?

Jeff_Perkinson
Community Manager Community Manager

Re: how to query an external data base to the existing data table in JMP

The SQL Query Builder in JMP will do this for you automatically. No need to write the JSL yourself.

 

As a part of the query you can build a filter. That filter can import data by matching values from an existing data table, exactly as you're trying to do here.

 

After building the query you can save the the Run Script to get the JSL to run the query as a part of a larger script. 

 

 

-Jeff