June 18, 2009 at 7:26 am
SELECT
distinct
SD.ERP_ORDER,
SH.SHIPMENT_ID,
SH.INTERNAL_SHIPMENT_NUM,
SC.ITEM,
SC.LOT,
SN.SERIAL_NUMBER,
SUM(SC.QUANTITY_UM)as Total, ---cause error
SH.SHIP_TO_NAME
FROM
SHIPPING_CONTAINER SC WITH(NOLOCK)
JOIN SHIPMENT_DETAIL SD
ON SC.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
JOIN SHIPMENT_HEADER SH
ON SC.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
JOIN SERIAL_NUMBER_VIEW SN
ON SN.SHIP_CONT_NUM = SC.INTERNAL_CONTAINER_NUM
WHERE
SH.INTERNAL_SHIPMENT_NUM = '182823'
AND SD.ITEM IN ('X0587100','X0587200','X0587500','X0587000','X0586600','X0586800'
,'X0586700','X0586900','X0587600','X0587400','X0587300')
AND SC.LOT >= '1208'
AND SN.SERIAL_NUMBER IS NOT NULL
--AND SC.CONTAINER_ID IS NOT NULL
GROUP BY
SD.ERP_ORDER,
SH.SHIPMENT_ID,
SH.INTERNAL_SHIPMENT_NUM,
SC.ITEM,
SC.LOT,
SN.SERIAL_NUMBER,
SH.SHIP_TO_NAME
June 18, 2009 at 7:44 am
What error message are you getting from this SQL, I can see a distinct in there, you are using a Group function, that will give you distinct, can you post what error message you are getting on this 🙂
June 18, 2009 at 8:08 am
your SC.QUANTITY_UM column must be containing varchar data?
June 18, 2009 at 8:17 am
you are absoutely right, I got it to work now. thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply