A newbie question :)

Wats wrong in this 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]

What makes you say there is something wrong with this query?
In other words: we need some clues - are there any error messages, perhaps the results are not what you expected them to be ...

Yes Adriaan. The result is not as expected. I am using Navision Tables( Navision- Microsoft Dunamivs Nav). The transfer Line table contains 37 records. I am getting those 37 records. But Since the 3 quantity fileds are calulated from other tables their values are not coming correct.
For example the Inventory filed is nothing but the sum of Item ledger entry's Quantity column where item no := transfer lines Item no.
and Qty on Prod Order(released) is the sum of prod_ Order Line's Remaining Quantity(Base) Column where item No. = transfer lines Item no. and only those line whioch have status = 3
Same with other.
I hope this will clear somewhat.

So there are 37 rows in the master table, and the query returns 37 rows with some aggregate details from detail tables.
You're not describing what you expect to get, and in what way the results from the query are different.
I already thought they were funny column names, but now that you mention it, someone else will know more about issues with RS.

I dont see any syntax errors. Looks like an issue with the logic.
Since you are joining more than one table and it looks like some values are added more than once. I see your grouping and joining are on [Transfer Line] The other tables are not joined among themselves.
If you remove aggregate function, and join the tales can u see the data duplicated (on those three "Quantity" columns).
You may have to add some more conditions or need to separate the queries.
Hope this helps.

Hi,
Thanks for your promp replies.
Ok let me explain more about wat i want to acheive. I have a transfer Line Table with 37 records and have fields Document No, Item No. etc.
The other Tables are Item Ledger entry and Prod. Order Lines.
What i want to acheive is take all lines from Transfer line table sum up the quantities of that item in Item Ledger entry and in Prod Order Lines with Status = 3
Say for example.
In Transfer Lines i have the following data
Document No. Item No.
ABC123 I0010
ABC124 I0012
ABC234 I0010
When it gets the first record from transfer line it goes to Item Ledger Entry table and sum up all the quantities for Item I0010(say for example the sum is 10)
Similarly it goes to Prod Order Table and sum the quantities for Item I0010 but only for those lines where the value of column called Status is 2(Say it comes as 12).
So the result should come like
Document No. Item No. Inventory Qty On Prod Line(Released)
ABC123 I0010 10 12
ABC124 I0012 50 90
ABC234 I0010 10 12
The first and last records have same Value for Inventory and Qty on Prod Order Line(Released) because these are for same items.

Hi,
I too was trying to say the same thing.
Let me say For Transfer Line 1 ( Assume that it has Docuemnt No as ABC 123 and Item No. 10010) has two entries in ProdOrder and three entries in ProdOrderReleased. So your query will return 6 entries as you do not have any connection between ProdOrder and ProdOrderReleased.
My suggestion is you need to have two different queries unless you find the missing link.
Hope this helps.

[quote user="preethi"]So your query will return 6 entries as you do not have any connection between ProdOrder and ProdOrderReleased.[/quote]
This line may give different meaning: It should be like this:
So your query will process 6 entries for Transfer Line 1 as you do not have any connection between ProdOrder and ProdOrderReleased
ProdOrder has this entry
Doc No Item No Qty
ABC 123 10010 1
ABC 123 10010 2
ProdOrderReleased has this entry
Doc No Item No QtyBase
ABC 123 10010 4
ABC 123 10010 5
ABC 123 10010 6

Based on Join this will be the initial result
Doc No Item No Qty QtyBase
ABC 123 10010 1 4
ABC 123 10010 1 5
ABC 123 10010 1 6
ABC 123 10010 2 4
ABC 123 10010 2 5
ABC 123 10010 2 6
Now the aggregate function will add the Qty and QtyBase to create the sum
Doc No Item No Sum(Qty) Sum(QtyBase)
ABC 123 10010 9 30
But, if you query seperately, it will be like this
Doc No Item No Sum(Qty)
ABC 123 10010 3
Doc No Item No Sum(QtyBase)
ABC 123 10010 15

Hope this gives further clarification.

HI,
I think you didnt get my Point.
I have 3 Tables as per above query.
Transfer Line
Document No. Item No. Item Description
XYZ Item 1 First Item
XYZ Item 2 Second Item
ABC Item 3 Third Item
ABC Item 2 Second Item
PQR Item 1 First Item
Item Ledger Entry As ILE
Entry No. Item No. Quantity
1 Item 1 4
2 Item 1 3
3 Item 2 5
4 Item 3 2
5 Item 2 2
6 Item 1 1
7 Item 3 10
8 Item 3 4
Prod. Order Line
Prod Order No. Status item No. Quantity
WO-111234 2 Item 1 100
WO-111113 3 Item 3 50
WO-111009 1 Item 2 100
WO-111678 2 Item 1 195
WO-111990 3 Item 1 200
WO-187645 3 Item 1 120
The ILE, Prod Order Line are related to Transfer Line table with Item No_.
Query should work like this.
Get the first record from Transfer Line Table. Take the Item_No.(In this case it is Item 1)
Go to ILE table with ILE.Item No_ = Transfer Line. Item No_
sum of the quantity fields in ILE table based on this condition

• Result Till here

Document No. Item No. Inventory(sum of Quantity field in ILE)
XYZ Item 1 8
now go to Prod. Order Line Sum up the quantities where Prod. Order Line.Item No. = Transfer Line.Item No. AND Prod. Order Line.Status = 3

• Result Till here

Document No. Item No. Inventory(sum of Quantity field in ILE) Qty on Released Prod Order
XYZ Item 1 8 320
Again go to Prod. Order Line Table Sum of the quantities Prod. Order Line.Item No. = Transfer Line.Item No. AND Prod. Order Line.Status = 2

• Result Till here

Doc No. Item No. Quantity(sum of Qty field in ILE) Qty on Prod Order Line(Status 3) Qty on Prod Order Line(Status 2)
XYZ Item 1 8 320 295
So the final result should come like

Doc No. Item No. Quantity(sum of Qty field in ILE) Qty on Prod Order Line(Status 3) Qty on Prod Order Line(Status 2)
XYZ Item 1 8 320 295XYZ Item 2 7 0 0
ABC Item 3 16 50 0
ABC Item 2 7 0 0
PQR Item 1 8 320 295

Looks like the feels are mutual


Try something like this:
SELECT a.[Document No.], a.[Item No.], a.[Item Description], a.ILEQty, b.POLQty
FROM
(SELECT TL.[Document No.], TL.[Item No.], TL.[Item Description],
SUM(ILE.Quantity) as ILEQty
FROM [Transfer Line] TL INNER JOIN [Item Ledger Entry] ILE ON TL.[Item No] = ILE.[Item No] --and additional conditions
GROUP BY TL.[Document No.], TL.[Item No.], TL.[Item Description]) AS a
INNER JOIN
(SELECT TL.[Document No.], TL.[Item No.], TL.[Item Description],
SUM(POL.Quantity) as POLQty
FROM [Transfer Line] TL INNER JOIN [Product Order Line] POL ON TL.[Item No] = POL.[Item No] --and additional conditions
GROUP BY TL.[Document No.], TL.[Item No.], TL.[Item Description]) as b
On a.[Document No.] = b.[Document No.] and a.[Item No.] = b.[Item No.]

Hi,Thnaks for your effort dear but this is not i want. With this first i am not getting all the lines for TRansfer Lines Tables second suppose if i have 3 transfer line for an Item thn output is coming with 6 lines.

Okay,
I see two issues here

1. you need to change the inner join into LEFT JOIN
2. I haven't added your conditions on status. You need to add them.

Hope this helps.

Hi Preethi,I wrote the code like this but still not geeting the desired result what i am expecting and as explained in my previous mail.May i have you yahoo or Skype Id so that we can share more on this. I will be grateful to you.----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT DerivedTable1.[Document No_], DerivedTable1.[Item No_], DerivedTable1.Quantity, DerivedTable1.[Transfer-from Code], DerivedTable1.[Transfer-to Code], DerivedTable1.Description, DerivedTable1.Inventory, DerivedTable2.QtyonRPOFROM (SELECT TL.[Document No_], TL.[Item No_], TL.Quantity, TL.[Transfer-from Code], TL.[Transfer-to Code], TL.Description, SUM(ILE.Quantity) AS Inventory FROM [CompanyName$Transfer Line] AS TL INNER JOIN [CompanyName$Item Ledger Entry] AS ILE ON TL.[Item No_] = ILE.[Item No_] GROUP BY TL.[Document No_], TL.[Item No_], TL.Quantity, TL.[Transfer-from Code], TL.[Transfer-to Code], TL.Description) AS DerivedTable1 INNER JOIN (SELECT TL.[Document No_], TL.[Item No_], TL.Quantity, TL.[Transfer-from Code], TL.[Transfer-to Code], TL.Description, SUM(ProdOrderLineRel.[Remaining Quantity]) AS QtyonRPO FROM [CompanyName$Transfer Line] AS TL INNER JOIN [CompanyName$Prod_ Order Line] AS ProdOrderLineRel ON TL.[Item No_] = ProdOrderLineRel.[Item No_] AND ProdOrderLineRel.Status = 3 GROUP BY TL.[Document No_], TL.[Item No_], TL.Quantity, TL.[Transfer-from Code], TL.[Transfer-to Code], TL.Description) AS DerivedTable2 ON DerivedTable1.[Item No_] = DerivedTable2.[Item No_]

An update on that happened offline, for all to understand the problem.
That table TransferLine didn't have uniqueness on documentNo + ItemNo. But the joins are only on ItemNo. So the result was not giving the required number of rows.
Solution: Create seperate Derived tables, to get the quantity totals grouped by ItemNo and Join them with TransferLine (LEFT JOIN) on ItemNo.
I was told that the query is working fine now.