topic How to SQL Query an external database and a saved JMP table? in Discussions

How to SQL Query an external database and a saved JMP table?

david707 — Tue, 20 Feb 2018 12:09:16 GMT

Hi,

Within a JMP script I really need to query both a saved JMP table and a seperate database table, but cannot work out how or find a solution online. There is a simplified example code below. The issue is once I have connected to the db I can no longer Query local tables. I know I could do a table join in JMP after the SQL Query on the external db, but the goal is to only pull customers specified in the customers.jmp table in the query to reduce how much data it needs to query. I have tried things such as JOIN 'cust', JOIN 'Customers' etc.

Any help would be greatly appreciated thanks.

// open JMP table
cust = Open( "***\JMP Tables\Customers.jmp");

//connect to DB
SQLConnection = Create database connection("DRIVER=*** SERVER=***; DATABASE=database; Uid=***;Pwd=***");

//Query both
PrePUSQL01 =
"
SELECT
T.CustomerID,
C.Inc
FROM database.dbo.table AS T
JOIN cust AS C ON C.CustomerID = T.CustomerID
WHERE C.Inc = '1'
"　
;
dt = Execute SQL(SQLConnection, 
PrePUSQL01,
"dt"
);

Re: How to SQL Query an external database and a saved JMP table?

Chris_Kirchberg — Tue, 20 Feb 2018 16:26:08 GMT

HI,

What I think you might need to do is read in the CustomerID values using GetValues (like in line one below) from the JMP table and then insert that list into the SQL code (using IN operatori for the WHERE clause)..

custIDList = :CustID << GetValues;

This isn't complete code, but a little push in the right direction.

You would have to use a little JSL to insert the list into the SQL code, but at least this would get you only the infromation from the customers that have an ID in the Customer.JMP file. Then you can use Join within JMP to merge the two sets together.

Hope that helps,

Chris

Re: How to SQL Query an external database and a saved JMP table?

pmroz — Tue, 20 Feb 2018 17:00:53 GMT

Chris is right - you need to create an IN list and include that in your SQL. This conversation may be helpful.

Re: How to SQL Query an external database and a saved JMP table?

Jeff_Perkinson — Tue, 20 Feb 2018 19:18:27 GMT

If I'm understanding you correctly, you have a JMP data table with a list of customers and you want to pull rows from a database that match those customers.

You can do this with the Match Column Values filter in Query Builder.

There's an example in the documentation for how to use the Match Columns Filter.