Recently, I am working on SQL.
A basic yet ambiguous question I have as follow: given a table A, which includes transactions information such as Transaction Number (TRX_NO), Customer Name (CUS_NAME), Customer Code (CUS_CODE) and Transaction time (TRX_TIME). Noted that for every transaction, there will be a record. For instance, when Mary goes to the shop on 20201024, 20201025, 20201031, then there are three records hold in this table.
With this in mind, if either using temp_table or cursor, how can I insert all first time data to another table B?
A more specific example, if the current table A stores three records of Mary, table B should hold the record of 20201024. In what way can I achieve this?
I have tried using cursor, but seems not a good try:
DECLARE var_VIP VARCHAR2(20);
CURSOR cur_FIRST IS
SELECT A.CUS_CODE
FROM TABLEA A
ORDER BY CUS_CODE, TRX_DATE, TRX_TIME;
--OPEN AND START CURSOR
BEGIN
OPEN cur_FIRST;
LOOP
FETCH cur_FIRST INTO var_VIP;
EXIT WHEN cur_FIRST%NOTFOUND;
INSERT INTO TABLEB B
(SELECT*
FROM TABLEA A
ORDER BY TRX_DATE, TRX_TIME);
END LOOP;
CLOSE cur_FIRST;
Thanks a lot for help!!!
I wrote down a shorter example which will help you understand the concept.
--create the tables
CREATE TABLE TABLEA (CUS_CODE INTEGER,
TRX_DATE DATE);
CREATE TABLE TABLEB (CUS_CODE INTEGER,
TRX_DATE DATE);
--insert some values
insert into TABLEA values (1, SYSDATE);
insert into TABLEA values (1, SYSDATE+1);
insert into TABLEA values (1, SYSDATE+2);
insert into TABLEA values (2, SYSDATE);
insert into TABLEA values (2, SYSDATE+1);
insert into TABLEA values (3, SYSDATE+2);
The procedure code:
DECLARE
CURSOR cur_FIRST IS
SELECT A.CUS_CODE, min(A.TRX_DATE) as TRX_DATE
FROM TABLEA A
GROUP BY CUS_CODE;
var_VIP cur_FIRST%rowtype;
--OPEN AND START CURSOR
BEGIN
OPEN cur_FIRST;
LOOP
FETCH cur_FIRST INTO var_VIP;
EXIT WHEN cur_FIRST%NOTFOUND;
INSERT INTO TABLEB VALUES (VAR_vip.CUS_CODE, VAR_VIP.TRX_DATE);
END LOOP;
CLOSE cur_FIRST;
END;
And check the result:
SELECT * FROM TABLEB;