I am trying to write a query utilizing data from two servers. My code looks like this:

SQL SQLORA SET SERVER server1
SQL SQLORA PREPARE SQLOUT FOR

[code]

;

END
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS firstdata
END

SQL SQLORA SET SERVER server2
SQL SQLORA PREPARE SQLOUT2 FOR

[code that wants to access my output from the first query...doesn't seem to recognize the existence of the firstdata table]

END
TABLE FILE SQLOUT2
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE-NUM OFF
END

As always any help would be greatly appreciated, thanks!

This message has been edited. Last edited by: FP Mod Chuck,

In your example, the HOLD file is a binary temp file on disk not an Oracle table. I doubt you'll be able to write SQL Passthru code utilizing that HOLD file. How are you trying to access the file created from the first query?

I simply want to join the file created from the first query to a table that resides on server2. Utilizing SQL, if possible, but if that is not an option I can try to do it in WebFocus language.

You will need write access on the server1.
Also, server2 must be able to read server1.
But you should be able to do the following (or similar to):

SQL SQLORA SET SERVER server1
SQL SQLORA PREPARE SQLOUT FOR

[code]

;

END

ENGINE SQLORA SET DEFAULT_CONNECTION Connexion_ToServer1

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS firstdata FORMAT SQLORA
END

SQL SQLORA SET SERVER server2
SQL SQLORA PREPARE SQLOUT2 FOR

Connexion_ToServer1 must be defined as a valid connexion in RS that will point to server1 and a valid DB where you have (or the generic user that runs the query) the write access.

But then, I'm not sure if the "firstdata" will already be available to query against it from a direct SQL.

This message has been edited. Last edited by: MartinY,

The problem with writing this into an Oracle instance is that it'll persist in the DBMS. DBA's usually don't like users writing stuff into their databases. If all you want to accomplish is to join the answer sets from two Oracle instances, you could create two hold files and join them for the final output. That way the hold files will go away once you're done.

By the way I think you should be able to create master files for the two Oracle servers and use WebFOCUS to join them. In that case you'll have to use WebFOCUS code to accomplish the query instead of SQL. That might be a better approach.

This message has been edited. Last edited by: BabakNYC,

I found a kind of embarrassingly easy fix; I was doing what I was doing because I only needed to access a single table on server 2, but apparently I could pull that into my original query simply with the following:

tablename@server2 alias

I think I overcomplicated things when trying to explain what I was trying to do. Thanks for your help Babak and Martin.