View containing data from multiple databases

[Former Member]

Hi

In a 3rd party integration szenario I have the following challenge:

On 1 Sybase server, there are 3 ERP companies stored in 3 dbs. The table structures on these 3 dbs are 100% identical. In my application I would like to show data from all 3 companies together in 1 view. Let's say, I want to see addresses from all 3 companies.

Is there a way to create a view containing data from more than 1 db (meaning from more than 1 company)?

Tanks in advance, your help is greatly appreciated. Heinz

[Former Member · Accepted solution]

Hello Heinz,

not sure if I got your question right, but if: You may create a remote server entry for each of your DBs that is not local, and then a proxy table for each of these tables. It'll probably be helpful to use a different owner for each remote user which will allow you to use identical sets of proxy user names. Finally, you create a view unioning (UNION ALL) the proxy tables (and a local table, if required). It's probably a good idea to add a column with a literal value unique to each branch of the union indicating the origin / physical location of the rows.
However, the optimizer's capabilities to optimize queries with non-trivial restrictions against such a view may be limited. You may have to use stored procedures with a result set as wrappers in cases where this causes problems.

HTH

Volker

[Former Member · Accepted solution]

Hi Volker Thank you for your answer. I probably don't have too many administrative rights on that Sybase server. If I got your answer right, there is nothing like "just a view" containing the rows of all companies? The DBs are all local on the same machine in the same database engine (in sql server this would equal to "in the same instance"). So maybe or should I say hopefully no need for remote servers? And yes, I this works, I will add a column as an indicator per row. Thanks much for clarifying. Heinz

[Former Member · Accepted solution]

That's one probably the most fundamental difference between SQL Anywhere and MSSQL (or ASE). You connect to a single database, not to a server. Connectionwise, databases on the same server are just as separate as they would be on different servers. At the same time, you don't need administrative rights on the server, but the specific rights to create proxy objects in the database holding them plus credentials for the database you try to access via proxy.

HTH

Volker

[Former Member · Accepted solution]

Ouch, seems to be my misconception ;-(

When reading about possible solutions, I found this post on Stackoverflow. https://stackoverflow.com/questions/31113458/how-to-join-tables-in-different-database-on-the-same-sy...

Made me thinking it should be possible to just change the DB within the same connection. Just out of curiosity: Whats going on in the following snippet? Do they change the DB anyway, or do I misundertand the Term DB?

select tabA.*,tabC.* from DatabaseB..TableA tabA, DatabaseA..TableC tabC where tabA.xxx = tabC.xxx

Thanks again to bring some light into it. Very appreciated Heinz

[Former Member · Accepted solution]

The Stackoverflow is about SAP (formerly Sybase) Adaptive Server Enterprise aka ASE. If you're using ASE, it works precisely like that, but it would mean that you're asking in the wrong forum. If you're using SAP (formerly Sybase) SQL Anywhere, you're in the right place here, but the Stackoverflow post doesn't match your environment.

HTH

Volker

[VolkerBarth · Accepted solution]

Just to add to ASE: If you are familiar with MS SQL, ASE is in many respects rather similar because both share a common origin...

[Former Member · Accepted solution]

I was not aware about the difference between this 2 products. But a least, I'm in the right forum here. Thanks for sharing your knownledge!