I have a table in HANA that has the following columns: Customer(varchar), Day(varchar), Item(varchar), Quantity (decimal), Cost(decimal)
My goal is to have a sql procedure that will duplicate the table values and append them to the existing table daily, while also updating the Day column values with the next day. So it will just be the same data over and over but new values for the Day column.
I believe this needs a select * from the table into a variable, then loop through the variable in which the Day column values will push forward 1 day, and then an insert all the updates rows. I'm stuck in the part of testing out the selection of 1 column and declaring it into a variable, and keep receiving this error:
DO
BEGIN
DECLARE V1 VARCHAR(20);
SELECT 'ITEM' INTO V1 FROM "TABLE_NAME";
SELECT :V1 FROM "TABLE_NAME";
END;
DBTech JDBC: fetch returns more than requested number of rows: "TABLE_NAME"."(DO statement)": line 4 col 5 (at pos 43):
CodePudding user response：
if you want to double your values, you don't need loops or variables.
the following doubles all ITEMs with a curent timestamp
INSERT INTO "TABLE_NAME"
("ITEM", "MYDATETIME")
SELECT "ITEM", NOW ( )
FROM "TABLE_NAME";
CodePudding user response：
The requirement seems to be:
For each customer copy all entries of a reference day (e.g., the most recent entries) to new entries for the day following the reference day.
Such a function could be supporting e.g., the daily "roll over" of inventory entries.
In its most basic form of requirements this can be achieved in plain SQL - no procedure code required.
create column table cust_items
(customer nvarchar(20)
, i
tem_date date
, item nvarchar(20)
, quantity decimal (10,2)
, cost decimal (10,2)) ;
insert into cust_items values ('Aardvark Inc.', add_days(current_date, -3), 'Yellow Balls', 10, 23.23);
insert into cust_items values ('Aardvark Inc.', add_days(current_date, -3), 'Bird Food', 4.5, 47.11);
insert into cust_items values ('Aardvark Inc.', add_days(current_date, -3), 'Carrot Cake', 3, 08.15);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -3), 'Red Ballon', 1, 47.11);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -3), 'Black Bile', 2, 23.23);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -3), 'Carrot Cake', 3, 08.15);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -2), 'Red Ballon', 1, 47.11);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -2), 'Black Bile', 2, 23.23);
insert into cust_items values ('Wolf Ltd.', add_days(current_date, -2), 'Carrot Cake', 3, 08.15);
select * from cust_items
order by item_date, customer, item;
/*
CUSTOMER ITEM_DATE ITEM QUANTITY COST
Aardvark Inc. 6 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 6 Apr 2022 Carrot Cake 3 8.15
Aardvark Inc. 6 Apr 2022 Yellow Balls 10 23.23
Wolf Ltd. 6 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 6 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 6 Apr 2022 Red Ballon 1 47.11
Wolf Ltd. 7 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 7 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 7 Apr 2022 Red Ballon 1 47.11
*/
We see that the two customers have individual entries, "Wolf Ltd." most recent entries are for April 7th, "Aardvark Inc." most recent entries are for April 6th.
The first part of the task now is to find the entries for most recent ITEM_DATE per customer. A simple join with a sub-query is sufficient here:
select co.customer, add_days(co.item_date, 1) as new_date, co.item, co.quantity, co.cost
from
cust_items co
join (select customer, max(item_date) max_date
from cust_items ci
group by customer) m_date
on (co.customer, co.item_date)
= (m_date.customer, m_date.max_date);
/* new date entries for each customer, based on previous most recent entry per customer
CUSTOMER NEW_DATE ITEM QUANTITY COST
Aardvark Inc. 7 Apr 2022 Yellow Balls 10 23.23
Aardvark Inc. 7 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 7 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 8 Apr 2022 Red Ballon 1 47.11
Wolf Ltd. 8 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 8 Apr 2022 Carrot Cake 3 8.15
*/
Note, the add_days(co.item_date, 1) as new_date function takes care of the "moving the day one day ahead" requirement.
The second part of the requirement is INSERTing the new entries into the same table:
insert into cust_items
(select co.customer, add_days(co.item_date, 1) as new_date, co.item, co.quantity, co.cost
from
cust_items co
join (select customer, max(item_date) max_date
from cust_items ci
group by customer) m_date
on (co.customer, co.item_date)
= (m_date.customer, m_date.max_date)
);
/* execute 3 times
Statement 'insert into cust_items (select co.customer, add_days(co.item_date, 1) as new_date, co.item, ...'
successfully executed in 25 ms 530 µs (server processing time: 14 ms 94 µs) - Rows Affected: 6
Statement 'insert into cust_items (select co.customer, add_days(co.item_date, 1) as new_date, co.item, ...'
successfully executed in 9 ms 288 µs (server processing time: 3 ms 900 µs) - Rows Affected: 6
Statement 'insert into cust_items (select co.customer, add_days(co.item_date, 1) as new_date, co.item, ...'
successfully executed in 11 ms 311 µs (server processing time: 4 ms 586 µs) - Rows Affected: 6
--> number of new records always the same as only the most recent values are copied
*/
The table content now looks like this:
/*
CUSTOMER ITEM_DATE ITEM QUANTITY COST
Aardvark Inc. 6 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 6 Apr 2022 Carrot Cake 3 8.15
Aardvark Inc. 6 Apr 2022 Yellow Balls 10 23.23
Wolf Ltd. 6 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 6 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 6 Apr 2022 Red Ballon 1 47.11
Aardvark Inc. 7 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 7 Apr 2022 Carrot Cake 3 8.15
Aardvark Inc. 7 Apr 2022 Yellow Balls 10 23.23
Wolf Ltd. 7 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 7 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 7 Apr 2022 Red Ballon 1 47.11
Aardvark Inc. 8 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 8 Apr 2022 Carrot Cake 3 8.15
Aardvark Inc. 8 Apr 2022 Yellow Balls 10 23.23
Wolf Ltd. 8 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 8 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 8 Apr 2022 Red Ballon 1 47.11
Aardvark Inc. 9 Apr 2022 Bird Food 4.5 47.11
Aardvark Inc. 9 Apr 2022 Carrot Cake 3 8.15
Aardvark Inc. 9 Apr 2022 Yellow Balls 10 23.23
Wolf Ltd. 9 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 9 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 9 Apr 2022 Red Ballon 1 47.11
Wolf Ltd. 10 Apr 2022 Black Bile 2 23.23
Wolf Ltd. 10 Apr 2022 Carrot Cake 3 8.15
Wolf Ltd. 10 Apr 2022 Red Ballon 1 47.11
--> only Wolf Ltd. has entries on 10/4 as it was the only one starting off with
values on 7/4.
*/