CODEWITHSUNDEEP
sqloracle-database
Mike Dole
Mike Dole

Reputation: 687

Oracle View from multiple tables with same kind of data

I have a view in Oracle with customer data from e.g. table DEBTABLENL:

SELECT DEBTABLENL.ACCOUNTNUMBER  AS DBDebnmbr
 , DEBTABLENL.ELCSEARCHCODE   AS DBNameShort
 , DEBTABLENL.NAME    AS DBName1
 , Etc, Etc FROM DEBTABLENL

Is it possible to extend the view to include the same kind of records from a different table, in this example DEBTABLEBE

SELECT DEBTABLEBE.ACCOUNTNUMBER  AS DBDebnmbr
 , DEBTABLEBE.ELCSEARCHCODE   AS DBNameShort
 , DEBTABLEBE.NAME    AS DBName1
 , Etc, Etc FROM DEBTABLEBE

Thus ending up with one view with all my customers from NL and BE. My BE customers are in a MSSQL database which I'll transfer daily to my Oracle database to get one big list. Because the Oracle View has to have the records I can't go the other way around..

Regards,

Mike

Upvotes: 0

Views: 1584

Answers (2)

user359040
user359040

Reputation:

Assuming both tables have the same structure, you could write your view as:

SELECT DEBTABLE.ACCOUNTNUMBER  AS DBDebnmbr
 , DEBTABLE.ELCSEARCHCODE   AS DBNameShort
 , DEBTABLE.NAME    AS DBName1
 , Etc, Etc 
FROM (SELECT * FROM DEBTABLENL UNION ALL SELECT * FROM DEBTABLEBE) AS DEBTABLE

If you have other tables joined to DEBTABLENL in your existing views, this approach means that you will only need to link them to the alias DEBTABLE once, instead of linking them separately to both DEBTABLENL and DEBTABLEBE and then UNIONing the results.

Upvotes: 0

Vikram
Vikram

Reputation: 8333

use union in both the queries like:

SELECT DEBTABLENL.ACCOUNTNUMBER  AS DBDebnmbr
 , DEBTABLENL.ELCSEARCHCODE   AS DBNameShort
 , DEBTABLENL.NAME    AS DBName1
 , Etc, Etc FROM DEBTABLENL
UNION ALL
SELECT DEBTABLEBE.ACCOUNTNUMBER
 , DEBTABLEBE.ELCSEARCHCODE
 , DEBTABLEBE.NAME
 , Etc, Etc FROM DEBTABLEBE

Upvotes: 1

Related Questions