SQL Report

sunnyk

Hi,
I am not sure whether it is right forum to ask this. But i hope you people will guide me on this. but wats wrong in the following query.

SELECT [Transfer Line].[Document No_], [Transfer Line].[Transfer-from Code], [Transfer Line].[Item No_], [Transfer Line].Description, [Transfer Line].Quantity,
SUM(ILE.Quantity) AS Inventory, SUM([Prod Order Lines].[Remaining Qty_ (Base)]) AS [Qty on Prod(Released)],
SUM([Prod Order Lines2].[Remaining Quantity]) AS [Qty On Prod(Firm Planned)], [Transfer Line].[Transfer-to Code]
FROM [Company$Item Ledger Entry] AS ILE RIGHT OUTER JOIN
[Company$Transfer Line] AS [Transfer Line] LEFT OUTER JOIN
[Company$Prod_ Order Line] AS [Prod Order Lines] ON [Transfer Line].[Item No_] = [Prod Order Lines].[Item No_] AND
[Prod Order Lines].Status = 3 LEFT OUTER JOIN
[Company$Prod_ Order Line] AS [Prod Order Lines2] ON [Transfer Line].[Item No_] = [Prod Order Lines2].[Item No_] AND
[Prod Order Lines2].Status = 2 ON ILE.[Location Code] = [Transfer Line].[Transfer-from Code] AND ILE.[Item No_] = [Transfer Line].[Item No_]
GROUP BY [Transfer Line].[Document No_], [Transfer Line].[Transfer-from Code], [Transfer Line].[Item No_], [Transfer Line].Description, [Transfer Line].Quantity,
[Transfer Line].[Transfer-to Code]

FDickschat

The Query itself works. What I find odd is that the FROM is the Item Ledger Entry table and not Transfer Line.

Maybe you should filter the TransferLine.Derived From Line No. Field = 0 but that depends on what you want to achieve.

What did you want to achieve or where is your problem?