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
TABLE FILE SQLOUT
ON TABLE HOLD AS firstdata
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]
TABLE FILE SQLOUT2
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE-NUM OFF
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?
WebFOCUS 8204, Unix, Windows
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,
WF versions : Prod 126.96.36.199M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
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,
WebFOCUS 8204, Unix, Windows
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:
I think I overcomplicated things when trying to explain what I was trying to do. Thanks for your help Babak and Martin.
|Powered by Social Strata|