How to split a single row into multiple rows based upon a number value

[ugomangini]

Hi all,

I am trying to split a single row into multiple rows based upon a specific number value.
For example, having a Document Line with "Quantity"=37 while OITM."SalPackUn"=10 I should obtain 4 SQL rows:
"ItemCode", 10

"ItemCode", 10

"ItemCode", 10

"ItemCode", 7
Is it possible to obtain this in Hana as it could be in Oracle or other dbms?
Thanks in advance.

[lbreddemann]

How would you do this dis aggregation on Oracle or other DBMS?

[ugomangini]

Two or three ways.

See samples here:

https://www.toolbox.com/tech/oracle/question/how-to-split-a-single-row-into-multiple-rows-based-upon...

Thanks

[lbreddemann]

What’s stopping you from using one of those solutions?

[ugomangini]

"CONNECT BY" and "LEVEL" are not supported by Hana syntax.
That's why I'm looking for a different approach.

[lbreddemann]

Ok, the link you provided leads to multiple solutions and some of them don’t use the connect by function. How about you post the code you want to use on HANA and then folks can come up with approaches to make it work instead of vaguely waving in the direction of your requirement and hope for an implementation of it?

[ugomangini]

Correct. But all of them use "level" keyword.
That's why I'm looking for a way to obtain to split a single row into multiple rows using a number value, using -HANA- syntax.

Suppose to have a query like:
SELECT T0."ItemCode", T0."Quantity", T1."SalPackUn" FROM DLN1 T0 INNER JOIN OITM T1 ON T0."ItemCode"=T1."ItemCode" WHERE T0."DocEntry"=53 AND T0."LineNum"=3

giving a single row like this:

"ItemCode","Quantity","SalPackUn"
"Pippo", 138, 40

I need to split it into 4 rows like this:

"ItemCode", "MyQuantity"
"Pippo", 40
"Pippo", 40
"Pippo", 40
"Pippo", 18

Where "MyQuantity" is the quantity contained in each package.

Thanks again.

[ugomangini · Accepted solution]

Well, here is how I solved it, in case it may be useful to anyone.
Obviously, change T0."DocEntry" and T0."LineNum" in WHERE clause.

SELECT Q2.GENERATED_PERIOD_START as "Package N°",
CASE WHEN ( Q1."Quantity" - (Q2.GENERATED_PERIOD_START-1) * Q1."SalPackUn") < Q1."SalPackUn"
THEN
(Q1."Quantity"- (Q2.GENERATED_PERIOD_START-1) * Q1."SalPackUn")
ELSE
Q1."SalPackUn"
END AS "PackageQty",
Q1.*
FROM
( SELECT T0."DocEntry", T0."Quantity", T1."SalPackUn",
CASE WHEN T1."SalPackUn" <> 0 THEN ROUND( T0."Quantity" / T1."SalPackUn",0, ROUND_UP) ELSE T0."Quantity" END AS "MaxPackages",
T1."ItemCode", T1."ItemName"
FROM "RDR1" T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T0."DocEntry"= 13151 AND T0."LineNum" =0
) Q1
INNER JOIN
( SELECT GENERATED_PERIOD_START FROM SERIES_GENERATE_INTEGER(1,1,10001)) Q2 ON Q1."MaxPackages" >= Q2.GENERATED_PERIOD_START