0

I have inherited a query which was done by a previous developer and he is linking about 6 tables with left joins and I am getting duplicating datafor some reason when a row is added into the Paliized table, its actually duplicating 2 rows which is kinda on and I cant figure it out

I am using Microsoft SQL Server 2014 in case that makes a difference

SELECT 
    pp.ProductionPlanID, pp.WeekStartDate, pp.WeekEndDate, 
    ppi.ProductionPlanItemID, ppi.SOPOrderReturnLineID, 
    bat.ProductionPlanBatchID, sop.DocumentNo, 
    cust.CustomerAccountName, bat.Name AS BatchName, 
    si.ItemID, si.Code AS StockCode, si.Name AS StockName, 
    sopLine.AllocatedQuantity, o.Quantity AS LineQty
FROM
    ProductionPlan pp 
LEFT JOIN
    ProductionPlanItem ppi ON pp.ProductionPlanID = ppi.ProductionPlanID   
LEFT JOIN
    ProductionPlanBatch ppb ON ppi.ProductionPlanBatchID = ppb.ProductionPlanBatchID 
LEFT JOIN
    SOPOrderReturnLine sopLine ON ppi.SOPOrderReturnLineID = sopLine.SOPOrderReturnLineID 
LEFT JOIN
    SOPOrderReturn sop ON sopLine.SOPOrderReturnID = sop.SOPOrderReturnID 
LEFT JOIN
    SLCustomerAccount cust ON sop.CustomerID = cust.SLCustomerAccountID 
LEFT JOIN
    StockItem si ON sopLine.ItemCode = si.Code 
LEFT JOIN
    ProductionPlanBatch bat ON ppi.ProductionPlanBatchID = bat.ProductionPlanBatchID 
LEFT JOIN
    Boxed boxed ON sopLine.SOPOrderReturnLineID = boxed.SopItemLineID 
                AND pp.ProductionPlanID = boxed.ProductionPlanID 
LEFT JOIN
    Palletized palletized ON sopLine.SOPOrderReturnLineID = palletized.SopItemLineID 
                          AND pp.ProductionPlanID = palletized.ProductionPlanID 
CROSS APPLY 
    (SELECT 1 FROM master..spt_values v
     WHERE v.type = 'P' 
       AND v.number < ISNULL(boxed.QtyBoxed, 0) - ISNULL(palletized.QtyOnPallet, 0)) o(Quantity)
WHERE
    ProductionPlanID = 5801244

Basically what happens in the software they remove a row from the Boxed and place a row in the Palletized table but the paltised table in this view is causing two rows to appear in the box table when it shouldn't be.

My main misunderstanding is this line of code.

CROSS APPLY (SELECT 1
             FROM master..spt_values v
             WHERE v.type = 'P' 
               AND v.number < ISNULL(boxed.QtyBoxed, 0) - ISNULL(palletized.QtyOnPallet, 0)) o (Quantity)

I have never seen the spt.values table before and I am not sure what exactly it is doing. I can't really supply data with this query but I am hopping some dba can help me understand where the duplication coming from.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave
  • 233
  • 2
  • 12
  • I would recommend using EXISTS instead of joining for the tables where you do not need any rows back and other joins aren’t dependent on e.g. productionplanbatch. This has shored up this issue for me in many cases! – PPJN Mar 16 '19 at 05:39
  • See: https://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-master-spt-values-and-what-are-the-meanings for details on "spt.values" – marc_s Mar 16 '19 at 07:06

0 Answers0