Merged Multiple Records is Truncated

[Former Member]

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

[Former Member]

Hi Noel,

Edy previously was right! A recordset can save you because if you assign the value to a string variable you are fine!

One other way in the case that you want to call this query outside SAPB1, is to make a recursivly SQL Scalar Function that will be return one NVARCHAR(MAX).

eg.

DECLARE @combinedString VARCHAR(MAX) 
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + DocNum
FROM ORDR WHERE DocEntry BETWEEN @FROM_DOCENTRY AND @TO_DOCENTRY

RETURN @combinedString

Then you can easy call

SELECT A=dbo.THE_NAME_THAT_WE_WILL_GIVE(FROM_DOCENTRY,TO_DOCENTRY)

Nice an easy with an one line SQL Query in you code!

Kind Regards,

Evangelos D. Plagianos

[Former Member]

Hi Maik, Edy and Eva,

Thank you very much for your reply.

Said (original) query produces truncated result when executed inside SAP..

However, when same logic is used in Crystal Reports, it shows the complete information.

Warm Regards,

Noel

[edy_simon]

Hi Noel,

Where did you run this script ?

By default the SSMS does not show the full string of 'for XML Path'.

If you google up, you can find many links that say you can modify this length.

But from my experience, this did not help me.

But if you run it from the DI API Recordset, I think you should get the whole string.

Regards

Edy

[maik_delly]

Hi Noel,

the answer depends on how you are executing the query .

Is it RecordSet, DataTable, formatted search or .Net SQL to fill a edittext ?

The resulting string is 254 characters and this is the maximum size for a short text type. So my answer would be to change your datasource type and / or cast the result in SQL to nvarchar(max) - something like :

SELECT cast('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('')
            ) AS NVARCHAR(max))
FROM ODLN T0
WHERE T0.DocNum = 19354

regards,

Maik