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

varshini — Tue, 07 May 2019 12:05:47 GMT

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

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

pmroz — Tue, 07 May 2019 12:33:58 GMT

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

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

Jeff_Perkinson — Tue, 07 May 2019 20:50:53 GMT

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.

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

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

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

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