cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
david707
Level III

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

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"
);
2 ACCEPTED SOLUTIONS

Accepted Solutions

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

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

 

Chris Kirchberg, M.S.2
Data Scientist, Life Sciences - Global Technical Enablement
JMP Statistical Discovery, LLC. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
www.jmp.com

View solution in original post

Jeff_Perkinson
Community Manager Community Manager

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

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.

 

JMPScreenSnapz174.png

 

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

 

 

 

-Jeff

View solution in original post

3 REPLIES 3

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

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

 

Chris Kirchberg, M.S.2
Data Scientist, Life Sciences - Global Technical Enablement
JMP Statistical Discovery, LLC. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
www.jmp.com
pmroz
Super User

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

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

Jeff_Perkinson
Community Manager Community Manager

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

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.

 

JMPScreenSnapz174.png

 

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

 

 

 

-Jeff