Oracle ODBC Connection - trying join table in the sql statement

myoungers

I would like to select data from more than one Oracle table using "Open Database".  I have tried in the past without success so I have been brings back all data into multiple JMP data tables and then using JMP's built in join functionality.  Our database tables are getting large so this really slows things down.

SELECT name_db.first, name_db.last, add_db.street, add_db.zip FROM name_db, add_db WHERE name_db.cust_id = add_db.cust_id

Thanks.

pmroz

I join tables all the time using Oracle ODBC in JSL.  Are you getting an error message in the log window? 

Can you run the query in a SQL tool such as SQL Navigator, Toad, PL/SQL Developer?

Regarding performance I suggest running EXPLAIN PLAN on your query to see if you need an index.

One thing I found out about issuing SQL queries in JSL is that ODBC wants the query to be all on one line.  No comments.  If you have a very long query you can split it up in the JSL code like this:

    sql_statement =

"SELECT rd.gen_id, " ||

"       cg.gen_name, " ||

"       rc.cal_id " ||

"  FROM myschema.gen cg, " ||

"       myschema.rdrug rd, " ||

"       myschema.rcalendar rc " ||

" WHERE rd.cal_id = rc.cal_id " ||

"   AND rd.gen_id = cg.gen_id " ||

"   AND rd.cal_id = " || char(this_calendar_id) ||

" ORDER BY 1 desc, 3";