SQL Question

etmccarthy13

Baseband Member
Messages
79
Location
Fort Wayne, IN USA
Ok Im not sure whats wrong with this Query but it is duplicating entries. One with the info im wanting and one with out the info... I want to get rid of the high lighted lines... Any Help is appreciated


BElow is SQL Statement and a screen shot of the OutPut

SQLSTART:
select '' as Blank,
ShipmentOrder.DocumentDate,
'' as Time,
ShipmentOrder.WarehouseBOLNumber,
'' as PartnerFunction,
'' as Partner,
ShipmentOrder.CustomerPONumber as CustomerPONumber,
ShipmentOrder.CustomerOrderNumber as CustomerOrderNumber,
ShipmentOrderDetail.CustomerLineNumber as RefItm,
ShipmentOrderDetail.PoLineNumber as Item,
ShipmentOrderPick.WarehouseSku,
ShipmentOrderPick.LotReferenceTwo as BatchNumber,
ShipmentOrderPick.Qty as QtyShipped,
'ZZC' as UOM, ShipmentOrderPick.FromPalletId as ExtShipUnitID
from ShipmentOrder, ShipmentOrderPick, ShipmentOrderDetail
where ShipmentOrder.OrderNumber = $P{OrderNumber}and
ShipmentOrder.WarehouseName = $P{WarehouseName} and
ShipmentOrder.FacilityName = $P{FacilityName} and
ShipmentOrder.CustomerName = $P{CustomerName} and
ShipmentOrderPick.OrderNumber = ShipmentOrder.OrderNumber and
ShipmentOrderPick.WarehouseName = ShipmentOrder.WarehouseName and
ShipmentOrderPick.FacilityName = ShipmentOrder.FacilityName and
ShipmentOrderPick.CustomerName = ShipmentOrder.CustomerName
Group by
ShipmentOrder.DocumentDate,
ShipmentOrder.WarehouseBOLNumber,
ShipmentOrder.CustomerPONumber,
ShipmentOrder.CustomerOrderNumber,
ShipmentOrderPick.WarehouseSku,
ShipmentOrderPick.LotReferenceTwo,
ShipmentOrderPick.FromPalletID,
ShipmentOrderDetail.CustomerLineNumber,
ShipmentOrderDetail.PoLineNumber,
ShipmentOrderPick.Qty

Order By ShipmentOrderPick.WarehouseSku
SQLEND:
 

Attachments

  • SQL Screenshot.PNG
    SQL Screenshot.PNG
    18 KB · Views: 4
Moved to Programming board.

It's possible that you need to be doing Joins (inner joins?) on your tables rather than just select statements so that you're not getting duplicate data.

What version of SQL Server are you running?
 
Ive figured this out.. I to link the actual orders in both data bases using a where clause

Where ShipmentOrderDetail.OrderNumber = ShipmentOrder.OrderNumber

This then Links all 3 tables by order Number

ISSUE SOLVED!

REPORTFILENAME=N/A
REPORTTYPE=xls
SQLSTART:
select '' as Blank,
ShipmentOrder.DocumentDate,
'' as Time,
ShipmentOrder.WarehouseBOLNumber,
'' as PartnerFunction,
'' as Partner,
ShipmentOrder.CustomerPONumber as CustomerPONumber,
ShipmentOrder.CustomerOrderNumber as CustomerOrderNumber,
ShipmentOrderDetail.CustomerLineNumber as RefItm,
ShipmentOrderDetail.PoLineNumber as Item,
ShipmentOrderPick.WarehouseSku,
ShipmentOrderPick.LotReferenceTwo as BatchNumber,
ShipmentOrderPick.Qty as QtyShipped,
'ZZC' as UOM, ShipmentOrderPick.FromPalletId as ExtShipUnitID
from ShipmentOrder, ShipmentOrderPick, ShipmentOrderDetail
where ShipmentOrder.OrderNumber = $P{OrderNumber}and
ShipmentOrder.WarehouseName = $P{WarehouseName} and
ShipmentOrder.FacilityName = $P{FacilityName} and
ShipmentOrder.CustomerName = $P{CustomerName} and
ShipmentOrderPick.OrderNumber = ShipmentOrder.OrderNumber and
ShipmentOrderDetail.OrderNumber = ShipmentOrder.OrderNumber and
ShipmentOrderPick.WarehouseName = ShipmentOrder.WarehouseName and
ShipmentOrderPick.FacilityName = ShipmentOrder.FacilityName and
ShipmentOrderPick.CustomerName = ShipmentOrder.CustomerName and
ShipmentOrderDetail.WarehouseSku = ShipmentOrderPick.WarehouseSku
Group by
ShipmentOrder.DocumentDate,
ShipmentOrder.WarehouseBOLNumber,
ShipmentOrder.CustomerPONumber,
ShipmentOrder.CustomerOrderNumber,
ShipmentOrderPick.WarehouseSku,
ShipmentOrderPick.LotReferenceTwo,
ShipmentOrderPick.FromPalletID,
ShipmentOrderDetail.CustomerLineNumber,
ShipmentOrderDetail.PoLineNumber,
ShipmentOrderPick.Qty

Order By ShipmentOrderPick.WarehouseSku
SQLEND:
 
Back
Top Bottom