Union Query Between SQL and Oracle

[DeeRuff1]

Hi There,
I have a problem. I am working in VB.NET and I am doing a call where I have to create a union query that will combine results from a SQL Server database and an Oracle database. These results are populated into a grid. The problem is I am not sure how to create two different connections and then use them to execute the union query and then fill the same grid. Here is an example of the query - the top half comes from a SQL Server database and the bottom half comes from the ORACLE database.

select
s.name, g.interfacecode
from
DSDB2.dbo.GenericHistory g,
DSDB2.dbo.sites s,
DSDB2.dbo.domains d
where
s.site_id = g.site_id
and d.domain_id = g.domain_id
and g.interfacecode = '11'

union

select
DCM.SITE_NAME as name,
DCM.INTERFACE_CODE as interfacecode
from
DC_MASTER DCM,
DC_T1 DCT
where
DCM.PARENT = DCT.GROUP_ID
and DCM.INTERFACE_CODE = '11'

 

[Turkbear]

Hi,
I do not believe that you can do it that way..The 'Select...Union...' code expects to be run on ONE connection only ( it is a sentence, after all, and splitting it would not work)..

You can set up a Heterogenous connection in Oracle and use the SqlServer database like any other remote Oracle instance ( using a Database link) -OR import/link the data into Access from both databases and select from the Access database.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Dee,

Not wanting to be contrary with my friend, Turkbear, but yes, you should be able to do a UNION query amongst tables from any database vendor. I saw an Enterprise TenFold query one time that seemlessly combined data in one query from Oracle, Sybase, SQL Server, DB2, MySQL, and EnterpriseDB tables. So, I know you can do what you want...I just can't give you an example presently -- I must do some digging first.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
No problem Santa, if there is a way, I will be happy to learn something new..

However, it is not the UNION process itself that is causing me to doubt, but the fact that the UNION statement cannot be 'seen' by 2 database connections simultaneously.
( I do not see how you can reference 2 connections in one statement and have BOTH connections see the entire statement, at least not in any VB code I have seen..)

( In Oracle it would be simple:
Select * from OracleTable -- Local Oracle Instance table
UNION
Select * from SqlTable@SqlServerLink
)

But I am ready to learn....




To Paraphrase:"The Help you get is proportional to the Help you give.."