73_en-US

liliconcom

Accessing Oracle tables via ODBCHi All,

I'm new to JMP and need to go into an Oracle database and turn about 50 tables into JMP tables using JSL.

I've found and can use the OPEN DATABASE command, but have several questions:

1) Once you've opened the database, how do you disconnect from it at the end?

2) The OPEN DATABASE Command accepts an SQL statement and a JMP table name. How do you get data from 50 tables and put them into 50 JMP tables? Use 50 OPEn DATABASE commands or is there another way to execute SQL to get data?

Thanks for your helpRe: Accessing Oracle tables via ODBCI recently had success with question two using JSL. First, make a list of your table names, enclosed in curly braces.

TableList = {"003", "004", "005", "Table6", "Table 34", ...};

Then, use a for-loop to step through the table list, constructing a query that you can feed to the "Open Database" command that you've already found. I can't paste the code because this message board interprets some of it as HTML and make it look funny.

---------------------------------------------------------------------------------
For( -insert for loop conditions here- //step through the list and concatenate query
NewQuery = "SELECT DATE, FIELD from " || TableList || " where FIELD < 1;";

currentDT = Open Database( "DSN=*****;UID=*****;PWD=*****;DBQ=*****, etc;", NewQuery);
---------------------------------------------------------------------------------

Sorry that looks so messy, but hopefully you get the idea.

To do this within the database, you would need to use 50 JOIN statements in your SQL query. Do each of the tables share a common field?