BookmarkSubscribe
Choose Language Hide Translation Bar
leontol
Community Trekker

How to change JMP default behavior (download the whole table) when connecting to the database?

I have JMP 11, and in the manual I found this: “You can use Structured Query Language (SQL) statements to control what you import from an external database. When you open a database file in JMP, you are actually sending an SQL statement to the database. By default, this statement gets all files and records in the database table.” I need to query a Postgresql database table, which is huge (~7 billion records, a few Tb in size), so I need to prevent JMP from downloading this table by default. Is there a way to run my own query by default, when I connect to the table? As soon as I click "Open Table" button in Database Open Table window, it sends the default statement to download this huge table from the db. The only thing I can do at this point, is to stop this query on db side, with "select pg_cancel_backend(pid);" command. Any suggestions about how to change this JMP behavior?

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to change JMP default behavior (download the whole table) when connecting to the database?

If JMP downloads the entire table when you click Open Table, don't click Open Table!

 

Click Advanced instead. This place is where you use SQL to query the database. You can type your own SQL in the box, use the table and field lists to select specific variables, or click Where to build a clause to select specific records (rows). You can import a query file and then execute it.

 

There is also File > Database > Query Builder. These are all documented in Help > Books > Using JMP.

Learn it once, use it forever!
2 REPLIES 2

Re: How to change JMP default behavior (download the whole table) when connecting to the database?

If JMP downloads the entire table when you click Open Table, don't click Open Table!

 

Click Advanced instead. This place is where you use SQL to query the database. You can type your own SQL in the box, use the table and field lists to select specific variables, or click Where to build a clause to select specific records (rows). You can import a query file and then execute it.

 

There is also File > Database > Query Builder. These are all documented in Help > Books > Using JMP.

Learn it once, use it forever!
leontol
Community Trekker

Re: How to change JMP default behavior (download the whole table) when connecting to the database?

Thank you Mark, it works great! So the right way to do it :

In File-Database-Open Table- need to click "Advanced" button (Not "Open Table", this one will try to download the whole DB to your laptop!) – in "SQL" window just put your query.

0 Kudos