Skip to Content
0
Former Member
Jul 27, 2016 at 08:38 PM

Merged Multiple Records is Truncated

159 Views

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