Once, I wrote a completely useless programme – see below. I like to retain it as I spent quite some time on it. But it is absolutely useless.
The idea it that we have some kind of a counter that is incremented by 1 if a subsequent record has the same value for a particular field. It needs to be reset at 1 in case of a new value.
I wrote a nice PL SQL programme to do this.
However Oracle has three functions that would have led to the same result: a lag function, a row_number analytical function and a rank function. So, useless, useless. But it was fun programming.
declare
cursor c_emp is select * from stagingzone.T9_EMPLOYMENT ;
r_emp c_emp%ROWTYPE;
ff_Person_ID number := 0;
ff_Employment_ID number := 0;
volgno number := 0;
volg number := 0;
stmt varchar(1000);
stmt1 varchar(1000):=”;
teller number :=0;
hx varchar(35);
hx1 varchar(35);
begin
stmt:= ‘drop table stagingzone.EMPLOYMENT_BU’;
SELECT COUNT(*) into TELLER FROM all_tables WHERE table_name = UPPER(‘EMPLOYMENT_BU’) and owner = UPPER(‘STAGINGZONE’);
IF (teller>0) THEN
dbms_output.put_line(stmt);
execute immediate stmt;
END IF;
stmt:= ‘create table stagingzone.EMPLOYMENT_BU as select * FROM stagingzone.T9_EMPLOYMENT where 1=0’;
dbms_output.put_line(stmt);
execute immediate stmt;
teller:=0;
open c_emp;
loop
fetch c_emp into r_emp;
teller := teller + 1;
exit when c_emp%NOTFOUND ;
hx:=q'[to_date(‘]’||to_char(r_emp.START_DATE_EMPLOYMENT,’DDMMYYYY’)||q'[‘,’DDMMYYYY’)]’;
hx1:=q'[to_date(‘]’||to_char(r_emp.END_DATE_EMPLOYMENT,’DDMMYYYY’)||q'[‘,’DDMMYYYY’)]’;
if (r_emp.PERSON_ID=ff_Person_ID)
then volgno := volgno + 1;
else volgno :=1;
end if;
if (r_emp.PERSON_ID=ff_Person_ID and r_emp.EMPLOYMENT_ID =ff_Employment_ID)
then volg := volg + 1;
else volg :=1;
end if;
r_emp.SEQUENCE_NUMBER_EMPLOYMENT := volg;
r_emp.SEQUENCE_NUMBER :=volgno;
ff_Person_ID:=r_emp.PERSON_ID;
ff_Employment_ID:=r_emp.EMPLOYMENT_ID;
stmt1 := q'[INSERT INTO stagingzone.EMPLOYMENT_BU(PERSON_ID,START_DATE_EMPLOYMENT ,END_DATE_EMPLOYMENT ,SEQUENCE_NUMBER_EMPLOYMENT, SEQUENCE_NUMBER, EMPLOYER_ID, EMPLOYMENT_ID) values(]’||
r_emp.PERSON_ID||’,’||hx||’,’||hx1||’,’||r_emp.SEQUENCE_NUMBER_EMPLOYMENT||’,’||r_emp.SEQUENCE_NUMBER ||’,’||r_emp.EMPLOYER_ID ||’,’||r_emp.EMPLOYMENT_ID ||’)’;
— if mod(teller,100) = 0 then dbms_output.put_line(stmt1);end if;
execute immediate stmt1;
end loop;
close c_emp;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Oops foutje’);
dbms_output.put_line(‘SQL Code ‘||SQLCODE);
dbms_output.put_line(‘Message ‘||SQLERRM);
end;
/