Selecting & Join multiple table from different DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting & Join multiple table from different DB

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-09-16 : 07:05:21

Hi Team.

I have 18 SAP databases.

The customer would like to see a cross section of income and expense across all 18 companies.

Each company has its own SAP db.

For example:
SBO_CompanyOne
SBO_CompanyTwo

Income table = INV1
Expense table = PCH1

So far I have this.

SELECT 'Company One' AS Company, SUM(TotalSumSy) AS Customers,
(SELECT SUM(TotalSumSy) AS Expr1 FROM SBO_CompanyOne.dbo.PCH1
WHERE (LineStatus <> 'C')) AS Suppliers
FROM SBO_CompanyOne.dbo.INV1
WHERE (LineStatus <> 'C')

This gives me the following.

Company Customer Suppliers
Company One 4162598.680000 2874958.470000

But how do I create a dataset that results in something that looks like this.

Company Customer Suppliers
Company One 4162598.680000 2874958.470000
Company Two 1920729.250000 2375304.100000

Any help would be appreciated.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-16 : 07:36:59

SELECT 'Company One' AS Company, SUM(TotalSumSy) AS Customers,
(SELECT SUM(TotalSumSy) AS Expr1 FROM SBO_CompanyOne.dbo.PCH1
WHERE (LineStatus <> 'C')) AS Suppliers
FROM SBO_CompanyOne.dbo.INV1
WHERE (LineStatus <> 'C')

UNION ALL

SELECT 'Company One' AS Company, SUM(TotalSumSy) AS Customers,
(SELECT SUM(TotalSumSy) AS Expr1 FROM SBO_CompanyTwo.dbo.PCH1
WHERE (LineStatus <> 'C')) AS Suppliers
FROM SBO_CompanyTwo.dbo.INV1
WHERE (LineStatus <> 'C')

- Lumbago

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-09-16 : 07:47:38

Thanks Lumbago...

learn something new everyday

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-16 : 08:01:25

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources