How to duplicate or add rows by value in column ?

[Former Member]

Hi,

Please help me out in duplicating rows by the value in a column.

for e.g. please have a look at the following link.

sql - Repeating rows based on column value in each row - Stack Overflow

In the example, there is a column called repeat and they managed to duplicate this in oracle. However I am having troubles in doing this in SAP HANA. It would be totally awesome if someone could help me out.

Thnx!

[lbreddemann · Accepted solution]

Wow... you guys really like to do complicated stuff, don't you?

Why not simply use an auxiliary table?

create column table mytable (id int, quantity int)

insert into mytable values (1, 2);
insert into mytable values (2, 4);
insert into mytable values (3, 6);

select id, quantity from mytable;

ID	QUANTITY
1	2
2	4
3	6

Now, we need an auxiliary table that holds the sequence of values for us.

An easy way to do that is to use the row_number() window function over an large but otherwise arbitrary result set.

I use the OBJECTS system table as this is always present and usually contains several thousand entries.

For the sake of simplicity I limit this to 1000 entries. If higher numbers are required a cross join of the OBJECTS table can easily produce those numbers.

create column table sequence (quantity int);

insert into "SEQUENCE" (
select row_number () over () 
from objects limit 1000);

Now we're good to go.

The whole thing is just a unequal join based on the quantity columns:

select mt.id, mt.quantity, s.quantity, mt.quantity - s.quantity as "rows_to_go"
from mytable mt inner join "SEQUENCE" s 
    on mt.quantity >= s.quantity 
order by mt.id, mt.quantity;

ID	QUANTITY	QUANTITY	rows_to_go
1	2	1	1
1	2	2	0
2	4	1	3
2	4	2	2
2	4	3	1
2	4	4	0
3	6	1	5
3	6	2	4
3	6	3	3
3	6	4	2
3	6	5	1
3	6	6	0

As you see, this generates the number of rows based on QUANTITY.

What actually astonishes me is that this is really a old SQL programming problem that has been solved a gazillion times before but apparently all that knowledge - available in books (anyone reading Joe Celko?) and websites - is getting ignored.

Anyhow, there you go. Now you know.

- Lars

[former_member210482 · Accepted solution]

Hi Lars,

That is too awesome..

Thanks,

Safiyu

[Former Member · Accepted solution]

Thanks Lars! It works. You are absolutely awesome!

[lbreddemann · Accepted solution]

cheers mate!

[Former Member]

Hi Deepak & Safiyu,

Thank you for your answers. To give you a bit of understanding in my SQL scripts: On my own project I have 2 tables: A & B.

I have created a temporary #table to insert the columns from A join B (The values will be inserted in T_table at the end).

CREATE LOCAL TEMPORARY TABLE "#table"  LIKE "<schema>::procedure.TT_table";

INSERT INTO "#table" SELECT A.col1, A.col2, B.col3, B.col4 from A JOIN B;

INSERT INTO T_table from #table;

So far the T_table displays the selected columns in a regular way, but I still haven't successfully used your scripts yet and I think that's because of the way of setting up a table, as I do not have the authorization to create column tables and insert values. I have to work with 2 existing tables created by a previous procedure.

So unfortunately I can not use the script provided by Deepak.

For the script of Safiyu I encounter the following problem:

"FOR cur2 in 0..cur1.col2 DO"

SAP HANA doesn't seem to recognize the "DO" part anymore... strange.

[former_member210482]

Hi Jimmy,

That should not be an issue. Try correcting your syntax. Or else go with the while loop like suggested by deepak.

Regards,

Safiyu

[Former Member]

Hi Jimmy,

That should not be an issue.

BEGIN

DECLARE V_DUP INT;

DECLARE CURSOR C_REPEAT FOR

SELECT A.JOB,A.QUANTITY,A.STATUS,B."REPEAT" FROM <SCHEMA>."REPEAT" as A

inner join

<SCHEMA>."REPEAT" as B

on A.JOB = B.JOB

;

create local temporary table #TT_TAB like <schema>."REPEAT";

FOR C1 AS C_REPEAT DO

V_DUP := C1."REPEAT";

  WHILE :V_DUP <> 0 DO

  INSERT INTO #TT_TAB VALUES (C1."JOB", C1."QUANTITY", C1."STATUS",C1."REPEAT");

  V_DUP := :V_DUP - 1;

  END WHILE;

END FOR;

END;

after the procedure is executed:

INSERT INTO T_table from #TT_TAB ;

However it is not a good idea to use Local/Global temporary tables inside a Stored Procedure. Instead my suggestion would be to create a table, under your schema(user id). Ideally you would be having autho to create tables and stored procedure under it.

[Former Member]

     "FOR cur2 in 0..cur1.col2 DO"

     SAP HANA doesn't seem to recognize the "DO" part anymore... strange.

Might be(not 100% sure) because, the syntax says as " READ" sql. Trying removing it and execute.

[former_member210482]

Hi Deepak,

This is not true. By READ we are restricting the procedure from performing ddl activities. And DO is not a ddl activity.

Regards,

Safiyu

[Former Member]

Hi Safiyu

All i meant to say was, Stored Procedure is of type "Read" only and we had an "Insert" statement in the DO part.

Which is not possible.

[former_member210482]

Hi Deepak,

Sorry my bad. I got in a wrong way..

Regards,

Safiyu

[former_member210482]

Hi Jimmy,

You can do it by creating a table with same structure as source table using

CREATE COLUMN TABLE <target schema>.<table name>

LIKE <source schema>.<table name>

Then write a procedure to insert values into this table based on your source table. Find the below code. I am making use of cursor to iterate through the records and a for loop to insert the values. here col2 is my repeat field...

CREATE PROCEDURE "schema"."repeat1"

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER

READS SQL DATA WITH RESULT VIEW tab2

AS

/*********BEGIN PROCEDURE SCRIPT ************/

BEGIN

DECLARE CURSOR Rep FOR

SELECT "col1", "col2" from "schema"."Table1";

FOR cur1 as Rep DO

    FOR cur2 in 0..cur1.col2 DO

     insert into "schema"."Table2" values(cur1.col1,cur1.col2);

  END FOR;

  END FOR;

END;

I have not tried executing this. So there might be syntax issues. But the logic will be right. Instead of insert you can also try select * from cur1 into "schema"."Table2". My intention of creating a new table was I didn't wanted to mess up the source table.

Regards,

Safiyu

[Former Member]

This can be achieved using cursor and while loop in a stored procedure

create column table <SCHEMA>."REPEAT"

(

JOB VARCHAR(2),

QUANTITY INT,

STATUS VARCHAR(10),

REPEAT INT

);

INSERT INTO <SCHEMA>."REPEAT"  VALUES ('1', 100, 'OK',2);

INSERT INTO <SCHEMA>."REPEAT"  VALUES ('2', 400, 'HOLD',0);

INSERT INTO <SCHEMA>."REPEAT"  VALUES ('3', 200, 'HOLD',1);

INSERT INTO <SCHEMA>."REPEAT"  VALUES ('4', 450, 'OK',3);

Stored Procedure :

DROP PROCEDURE <SCHEMA>."REPEAT_PROC";

CREATE PROCEDURE <SCHEMA>."REPEAT_PROC"()

LANGUAGE SQLSCRIPT

AS

BEGIN

DECLARE V_DUP INT;

DECLARE CURSOR C_REPEAT FOR

SELECT JOB,QUANTITY,STATUS,"REPEAT" FROM <SCHEMA>."REPEAT";

FOR C1 AS C_REPEAT DO

V_DUP := C1."REPEAT";

  WHILE :V_DUP <> 0 DO

  INSERT INTO <SCHEMA>."REPEAT"  VALUES (C1."JOB", C1."QUANTITY", C1."STATUS",C1."REPEAT");

  V_DUP := :V_DUP - 1;

  END WHILE;

END FOR;

END;