Skip to Content

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

Dec 28, 2017 at 10:07 AM


avatar image

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"

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
ERALPER YILMAZ 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

sql.png (7.4 kB)
10 |10000 characters needed characters left characters exceeded