Skip to Content
author's profile photo Former Member
Former Member

Merged Multiple Records is Truncated

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Jul 28, 2016 at 12:24 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 27, 2016 at 09:19 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 28, 2016 at 05:33 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.