Skip to Content

Pulling Financial Data from 2 DB's in SQL

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]
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 17 at 03:03 AM

    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

    Add comment
    10|10000 characters needed characters exceeded