Query between 2 tables into different databases

[Former Member]

Hi, Please help, I have 2 Databases, called SISPETRO and SB-PEDIDOS, runnig on the same server, and I have on Sispetro DB, tables NOTAS and ITENSNOTAS, I can join both, without problems, but I need to get a value from table FRETES on SB-PEDIDOS DB. This is more or less the code Im trying.

select notas.codClient, notas.quantity, itens.product, fretes.valueFrete from NOTAS notas left join ITENSNOTAS Itens on (notas.seq = itens.seq) left join FRETE Frete on (notas.CodTransp = Frete.CodTransp) where ....

Im doing this inside a Delphi Aplication, I didnt try it into Interactive SQL. There is another issue, I think, the database name has a less signal(-) into its name, and it may be consider a forbiden character.

[Breck_Carter]

Try these commands on the SISPETRO database; they define a "remote server" called SB-PEDIDOS which points to the database SB-PEDIDOS, and a proxy table for FRETES. The schema for proxy_FRETES exists on the SISPETRO database, but the data only exists on SB-PEDIDOS.

CREATE SERVER "SB-PEDIDOS" CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 12;DBN=SB-PEDIDOS';
CREATE EXISTING TABLE proxy_FRETES 
   AT 'SB-PEDIDOS...FRETES';
SELECT * 
  FROM proxy_FRETES 
 ORDER BY proxy_FRETES.pkey;

[Former Member]

Dear Mr Breck and Mr Glen, Thanks for your answer, I decided to let lazy behind. I paid more attention to documentation, as Glen told me, and found the entire process, create an external server, then external tables, everything throug SybaseCentral. And, my surprise, was exactly like you show above Mr Breck. Im learnig a lot with this forum, Thanks a lot.

[MCMartin]

We all learn a lot here 😉

[Former Member]

The documentation describes how to set up a remote server, and how to create a proxy table to enable you to refer to the FRETE table from a local query when connected to the SISPETRO database.

What precisely is the issue you're having with the database name?

[Former Member]

Yes, I saw this before, but the documentation I found are too simple, Im having trouble to name the tables correctly. This method, create proxy_table, works at running time or creates a table phisicaly into my database ? I can create this proxy table and then select data from it ? Joinning to other tables ? How to clear or destroy this table after.

The issue I told is related to minus signal into databasename, SB-Pedidos, I mentioned because I got an error on my query into delphi code, but, leave it.