executing SQL function for tables from different datastore

dataservices job design
1

HI,
Can we use SQL function in BODS to process records from table which resides in 2 different datastore/database.

shazi58 (BOB member since 2016-02-01)

2

Yes, if you can execute a SQL in the command interpreter of one of the databases that will have access to both via the login credentials provided to the datastore. That is a very narrow corner case and I am not sure it is what you are asking. It would occur if you had two datastores to databases that were connected as linked servers.

  • E

eepjr24 :us: (BOB member since 2005-09-16)

3

Thanks for the reply.

Here I am trying to join tables from diffrerent datastores/database (SQL SERVER and ORACLE) …

shazi58 (BOB member since 2016-02-01)

4

This is possible in DS, but not through a SQL function. You would need to do the join in memory, since neither database server can talk directly to each other or use a data transfer transform to temporarily stage the data on one of the servers so the database can perform the join for you. There is no tool that I know of that would allow what you are asking for directly, even data federators perform the joins in memory on their own server.

  • E

eepjr24 :us: (BOB member since 2005-09-16)