Cross Database Server Query in Eclipse SQL Explorer

[rafig]

Hi,
I have a link to an Oracle database as one server in my solution.
Another database server is using MS SQL Server.
I need to do an update statement using results from the Oracle back-end into the SQL Server tables.
I can't seem to work out the syntax for doing a cross database server join
I need to do an UPDATE, but was first trying to do a SELECT along the lines of

Code: Select all

select supplier_product_code,description from "rnoh_estock"."dbo"."es_product"
where es_product.supplier_product_code = "RNOHPOLVESQL"."PURCHASE_LINES"."pl_supplier_product_code" ;

(my Oracle db server is called 'efin', but in database explorer of SQL Explorer it is 'RNOHPOLVESQL' and my SQL server is called 'rnoh_estock' if that helps...)

Can anyone advise on how I might be able to do it??
Thanks

Rafi

[john.allen]

Hi Rafi,

You can't do cross vendor database joins in a query. Lots of other ways to accomplish what you want to do in Servoy but not directly in a query. For instance you can loop through your foundset of records you want to use in Oracle and then push/update whatever it is you want from them into your SQL Server database. I've done that many times here between SQL Server and Oracle.

[rafig]

Hi John,
thanks for the reply, I thought that might be the only way, so that is what I am going to do.

Rafi