CODEWITHSUNDEEP
sqlsql-serveroracle-databasejoin
dj.milojevic
dj.milojevic

Reputation: 214

Joining oracle with mssql table

When i execute following line i get the result:

select * from my_table@link;

But when i try to join linked table with other tables like in select i get error that says "z.id - invalid identifier"

SELECT

                e.USER_NAME,e.FULL_NAME,r.RFC_NUMBER,r.TYPE, 

                TO_CHAR(TRUNC(a.TOTAL_TIME/3600),'FM9900') || ':' ||
                TO_CHAR(TRUNC(MOD(a.TOTAL_TIME,3600)/60),'FM00') || ':' ||
                TO_CHAR(MOD(a.TOTAL_TIME,60),'FM00') as TOTAL_TIME,

                a.ASSIGN_DATE,a.TIME_START,a.TIME_STOP,
                r.SUBMITTED_BY,r.REGISTER_DATE,r.DESCRIPTION 


                FROM table_a a 
                JOIN table_r ON a.REQ_ID = r.REQ_ID 
                JOIN table_e e ON e.emp_id = a.emp_id

                LEFT JOIN my_table@link z ON e.emp_id = z.id

                WHERE a.ASSIGN_DATE > '01-JAN-2013' and a.ASSIGN_DATE < '01-JAN-2015'

                ORDER BY r.RFC_NUMBER;

table_a, table_r and table_e are oracle tables. table my_table is mssql table that is being accessed via link.

so my question is how to join oracle tables with linked mssql table?

Upvotes: 1

Views: 94

Answers (1)

David Faber
David Faber

Reputation: 12485

From your comments I believe the issue is that SQL Server uses case-sensitive column names by default (more specific information can be found at this link), while Oracle will only use case-sensitive column names if these are enclosed in double quotes ". So if the column id in your SQL Server table is lowercase, you'll need to use quotes around this column name in Oracle. That is, if you don't put quotes around a column name in Oracle, it assumes uppercase. So z.id will look for a column named ID while z."id" will look for a column named id. Specifically, this line:

LEFT JOIN my_table@link z ON e.emp_id = z.id

should be this:

LEFT JOIN my_table@link z ON e.emp_id = z."id"

Hope this helps.

Upvotes: 1

Related Questions