Hi,
I merged multiple delivery records into 1. However, the result gets truncated? What is the maximum length of the resulting field and how can I make it longer?
SCRIPT:
SELECT 'XYZ,PO:'
+ CAST(T0.NumAtCard AS VARCHAR(10))
+ (SELECT ',UPC:' + T1.ItemCode
+ ',QTY:' + LTRIM(STR(T1.DeliveryQty))
+ ',EXP:' + CONVERT(varchar(8), T1.ExpiryDate, 12)
+ ',LOT:' + T1.LotNo
FROM TWC_Customer_Delivery T1
WHERE T0.DocNum = T1.DeliveryNo FOR XML Path(''))
FROM ODLN T0
WHERE T0.DocNum = 19354
RESULT:
XYZ,PO:15MO3HCC,UPC:BL03107E,QTY:396,EXP:180327,LOT:6C219,UPC:BL03102E,QTY:252,EXP:180517,LOT:6E175,UPC:BL03102E,QTY:282,EXP:180517,LOT:6E206,UPC:BL03182,QTY:6,EXP:180526,LOT:6E245,UPC:BL03187,QTY:12,EXP:180410,LOT:6D044,UPC:BL03137,QTY:24,EXP:180530,LO <<<==== MORE IS EXPECTED HERE
Thanks,
Noel
SELECT 'AMZN,PO:' + CAST(T0.NumAtCard AS VARCHAR(10)) + (SELECT ',UPC:' + T1.ItemCode + ',QTY:' + LTRIM(STR(T1.DeliveryQty)) + ',EXP:' + CONVERT(varchar(8), T1.ExpiryDate, 12) + ',LOT:' + T1.LotNo FROM TWC_AmazonCom_Delivery T1 WHERE T0.DocNum = T1.DeliveryNo FOR XML Path(''))FROM ODLN T0WHERE T0.DocNum = 19354