SQL Server Performance

A newbie question :)

Discussion in 'SQL Server 2005 Reporting Services' started by alisag, Jan 21, 2010.

  1. alisag New Member

    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]
  2. Adriaan New Member

    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 ...
  3. alisag New Member

    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.
  4. Adriaan New Member

    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.
  5. FrankKalis Moderator

    The SQL that is produced from your query generator is not really readable, but it looks as if this bit
    FROM [Company$Item Ledger Entry] AS ILE RIGHT OUTER JOIN
    [Company$Transfer Line] AS [Transfer Line]
    is missing the ON clause.
  6. preethi Member

    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.
  7. alisag New Member

    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.
  8. preethi Member

    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.
  9. preethi Member

    [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.


  10. alisag New Member

    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 295
    XYZ Item 2 7 0 0
    ABC Item 3 16 50 0
    ABC Item 2 7 0 0
    PQR Item 1 8 320 295

  11. preethi Member

    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.]
  12. alisag New Member

    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.
  13. preethi Member

    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.
  14. alisag New Member

    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_]
  15. preethi Member

    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.

Share This Page