Subscribe Bookmark RSS Feed

Oracle ODBC Connection - trying join table in the sql statement

myoungers

Community Trekker

Joined:

Mar 20, 2012

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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";

2 REPLIES
Solution

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";

myoungers

Community Trekker

Joined:

Mar 20, 2012

It's working now.  I guess it may have been a split line or comment added on my original try.

Thanks for the help PMroz.