Skip to Content
0

Pulling Financial Data from 2 DB's in SQL

Feb 16 at 05:32 PM

50

avatar image

Experts,

I have found a thread Inventory on hand quantities accross two databases that showed the logic of pulling data from 2 SAP B1 DBs in SQL. When I run this in SQL it acts like it is getting the data from different sources, but it is the same - the SQL DB that is active when I execute the query....

Your assistance would be greatly appreciated.

SELECT 'Company_A' as 'company'
	,T0.[FormatCode] AS 'FormatCode'
	, T0.[AcctCode] AS 'AcctCode'
	, T0.[AcctName] AS 'AcctName'
	, SUM(T1.[Credit]-T1.[Debit]) AS 'Balance'
	, T0.[FatherNum] AS 'FatherNum'
	, T2.[RefDate] AS 'RefDate'
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
	WHERE T0.[ActType] in ('I','E') 
		AND DateDiff(YY,T2.[RefDate],GetDate()) = 0 
		AND T1.[Debit] != T1.[Credit]
		GROUP BY T0.[FatherNum], T0.AcctCode, T0.[FormatCode], T0.[AcctName], T2.[RefDate]
	Having SUM(T1.[Debit]-T1.[Credit]) != 0
--ORDER BY  company,T0.[FatherNum]


UNION ALL
SELECT 'Company_B' as 'company'
	,T0.[FormatCode] AS 'FormatCode'
	, T0.[AcctCode] AS 'AcctCode'
	, T0.[AcctName] AS 'AcctName'
	, SUM(T1.[Credit]-T1.[Debit]) AS 'Balance'
	, T0.[FatherNum] AS 'FatherNum'
	, T2.[RefDate] AS 'RefDate'
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
	WHERE T0.[ActType] in ('I','E') 
		AND DateDiff(YY,T2.[RefDate],GetDate()) = 0 
		AND T1.[Debit] != T1.[Credit]
		GROUP BY T0.[FatherNum], T0.AcctCode, T0.[FormatCode], T0.[AcctName], T2.[RefDate]
	Having SUM(T1.[Debit]-T1.[Credit]) != 0
--ORDER BY company, T0.[FatherNum]
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Jimmy Liang Feb 17 at 03:03 AM
1

Hello Marli,

It's possible to query different databases in the same sql instance. You will need to have the full name of each table, just like the post you were referencing.

In your example, if Company_A uses the database name "SBOA" and schema name "dbo"; and Company_B uses the database name "SBOB" and schema name "dbo"; you will need to specify the full name for each table like this:

SELECT *

FROM [SBOA].[dbo].[OACT] -- referencing OACT from dbo in Company_A using the full table name

UNION ALL

SELECT *

FROM [SBOB].[dbo].[OACT] -- referencing OACT from dob in Company_B using the full table name

Hope this helps,

Jimmy

Share
10 |10000 characters needed characters left characters exceeded