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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
I wrote a nice PL SQL programme to do this. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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;
\/<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3278","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3278","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3278"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3278\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3278"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}