Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Switching Servers
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Switching Servers
 Login/Join
 
Member
posted
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,


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 11 | Registered: October 17, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 8203, Unix, Windows
 
Posts: 1332 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 11 | Registered: October 17, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1804 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 8203, Unix, Windows
 
Posts: 1332 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 11 | Registered: October 17, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Switching Servers

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.