Skip to Content
avatar image
Former Member

I want to convert this SQL query to HANA (Bold statement)

SELECT T0."DocEntry", sum (T3."PriceBefDi") - sum (T3."Price" ) as DA, T0."DocNum",T0."TaxDate", T0."BPLName", T1."U_NAME", T0."CardCode", T0."CardName",T2."SlpName", case T0."DocTotalFC" when 0 then T0."DocTotal" else T0."DocTotalFC" end DT, T0."DocCur" , 'AR Inv' as Doc_Type, STUFF((SELECT distinct ',' + CONVERT(NVARCHAR, INV1."Whscode") FROM INV1 where T0."DocEntry" = INV1."DocEntry" "FOR" XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') Whscode FROM OINV T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" INNER JOIN OSLP T2 ON T0."SlpCode" = T2."SlpCode" INNER JOIN INV1 T3 ON T0."DocEntry" = T3."DocEntry" GROUP BY T0."DocNum", T0."DocTotal", T0."DocTotalFC", T0."DocCur",T0."TaxDate", T0."BPLName", T1."U_NAME", T0."CardCode", T0."CardName",T2."SlpName",T0."DocEntry"

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 29, 2017 at 08:02 AM

    Hello Omar,

    The SQL code that you want to convert is actually concatenating string values with a seperator, or actually maybe it is a better term it is aggregating string values and using a seperator between each string value

    Before SQL Server provided String_Agg function to concatenate string columns from different rows into a single variable, Transact-SQL programmers developing code on SQL Server was using string concatenation with For XML Path method

    Since SAP HANA database already supports String_Agg function, you can replace that code block with a suitable SQLScript code

    Here is a sample SQL code modified from string_agg function on HANA db

     string_agg(column_name, ', ' order by position) as columns

    Output is as follows for my test table

    Add comment
    10|10000 characters needed characters exceeded