Running an SQL across Several Companies

[Former Member]

Hello Experts...

Background: The Customer has three SAP B1 Companies all sitting on same server. Let’s call them S_Comp, U_Comp, and C_Comp.  They want an SQL to be launched in C_Comp and have it pick up all the data from the three companies and then have the data displayed together.  Just as an example and to make it simple, they want the SQL to provide a list of Customers (OCRD.CardCode and CardName) and a list of all Open Invoices (OINV.DocNum and DocTotal) from all three companies.  The Customer Number on the three companies is the same…

So the basic SQL is something like this:

SELECT

T0.CardCode,

T0.CardName,

T1.DocNum,

T1.DocTotal

FROM OCRD T0

LEFT OUTER JOIN OINV T1

ON T0.CardCode = T1.CardCode

WHERE T1.DocStatus = 'O'

Any idea how to do this???

Thanks in advance - Zal

[Former Member · Accepted solution]

Hi Zal,

You may try:

SELECT

T0.CardCode,

T0.CardName,

T1.DocNum 'C Comp Invoice',

T1.DocTotal,

T2.DocNum 'S Comp Invoice',

T2.DocTotal,

T3.DocNum 'U Comp Invoice',

T3.DocTotal

FROM OCRD T0

LEFT JOIN OINV T1 ON T0.CardCode = T1.CardCode AND T1.DocStatus = ‘O’

LEFT JOIN [S_Comp].dbo.OINV T2 ON T0.CardCode = T2.CardCode AND T2.DocStatus = ‘O’

LEFT JOIN [U_Comp].dbo.OINV T3 ON T0.CardCode = T3.CardCode AND T3.DocStatus = ‘O’

WHERE T0.CardType='C' AND T0.CardName Like '[%0]%'

This query result can separate invoices for 3 companies.

However, a better one is:

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocTotal], 'C Company' 'Company' FROM OINV T0

WHERE T0.DocStatus = 'O'

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocNum], 'S Company' FROM [S_Comp].dbo.OINV T0

WHERE T0.DocStatus = 'O'

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocNum], 'U Company' FROM [[S_Comp].dbo.OINV T0

WHERE T0.DocStatus = 'O'

ORDER BY  T0.[CardCode]

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi Zal,

Try this :

SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocNum] FROM SBODemoUS.dbo.OINV T0

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocNum] FROM SBODemo_Dallas.dbo.OINV T0

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocNum] FROM SBODemoIrvine.dbo.OINV T0

Note: Change your database name in above query.

Hope helpful.

[jitin_chawla]

Hi,

Check this if it works for you :

SELECT T0.CardCode, T0.CardName, T1.DocNum,T1.DocTotal

FROM [S_Comp]..OCRD T0

LEFT OUTER JOIN [S_Comp]..OINV T1

ON T0.CardCode = T1.CardCode

WHERE T1.DocStatus = 'O'

union all

SELECT T2.CardCode, T2.CardName, T3.DocNum,T3.DocTotal

FROM [U_Comp]..OCRD T2

LEFT OUTER JOIN [U_Comp]..OINV T3

ON T2.CardCode = T3.CardCode

WHERE T3.DocStatus = 'O'

union all

SELECT T2.CardCode, T2.CardName, T3.DocNum,T3.DocTotal

FROM [C_Comp]..OCRD T2

LEFT OUTER JOIN [C_Comp]..OINV T3

ON T2.CardCode = T3.CardCode

WHERE T3.DocStatus = 'O'

More joins can be made in the same way. The DB should be restored on the same server.

Kind Regards,

Jitin

SAP Business One Forum Team