How do you join two tables from different Oracle schemas using a subquery
728742Oct 19 2009 — edited Oct 19 2009I am trying to join two tables from different Oracle schemas using a subquery. I can extract data from each of the tables without a problem. However, when I combine the select statements using a subquery I get the Oracle error *'ORA-00936: missing expression'*. Since each SELECT statement executes on its own without error I don't understand what is missing. The result set I am trying to get is to match up the LINE_ID from PDTABLE_12_1 in schema DD_12809 with the MAT_DESCRIPTION from table PDTABLE_201 in schema RA_12809.
The query is as follows:
sql = "SELECT [DD_12809].[PDTABLE_12_1].LINE_ID FROM [DD_12809].[PDTABLE_12_1] JOIN " _
+ "(SELECT [RA_12809].[PDTABLE_201].MAT_DESCRIPTION " _
+ "FROM [RA_12809].[PDTABLE_201]) AS FAB " _
+ "ON [DD_12809].[PDTABLE_12_1].PIPING_MATER_CLASS = FAB.PIPING_MATER_CLASS"
The format of the query is copied from a SQL programming manual.
I also tried executing the query using a straight JOIN on the two tables but got the same results. Any insight would be helpful. Thanks!
Edited by: user11338343 on Oct 19, 2009 6:55 AM