SQL Query for multiple companies

Srivas

Hi all,

I want to write an SQL query (VIEW) to show Item ledger data from multiple companies (say A,B,C) {same database}
. I don't know how to do it (like including $).

Many Thanks in advance

ara3n

here is an example

SELECT 
'Hall Company',"Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date",
 COUNT_BIG(*) "$Cnt",SUM("Quantity") "SUM$Quantity",SUM("Invoiced Quantity") "SUM$Invoiced Quantity" 
 FROM dbo."Hall Company$Item Ledger Entry" 
 GROUP BY "Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date"
union
SELECT
'Second Company',"Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date",
 COUNT_BIG(*) "$Cnt",SUM("Quantity") "SUM$Quantity",SUM("Invoiced Quantity") "SUM$Invoiced Quantity" 
 FROM dbo."Second Company$Item Ledger Entry" 
 GROUP BY "Item No_","Entry Type","Variant Code","Drop Shipment","Location Code","Posting Date"

Srivas

Thanks a million. Is it possible to get flowfields like Cost Amount (actual)..

Thanks

ara3n

yes it is possible

kriki

ara3n wrote:

yes it is possible

No: not using the table where the flowfield is defined.
Yes: you need to write the select on the real table (you can also point to the indexed view but that can be dangerous if you change some SIFT).

ara3n

here is an example of getting flowfields

select [Entry No_], [Item No_], *,
(SELECT  sum([Cost Amount (Actual)])
			from [Hall Company$Value Entry] as V 
			where [Item Ledger Entry No_] =ILE.[Entry No_]) as CostAmountActual
from	[Hall Company$Item Ledger Entry]	as ILE