back to first page..
back to WRBD

-- verifying years and dates
select * from(
select op_id,extract(year from op_date) as dateyear,op_year from wrbd.operation_op where op_date is not null and op_year is not null) as sub
where dateyear!=op_year; -- op_id=1657
update wrbd.operation_op set op_date='1977-01-01' where op_id=1657;
update wrbd.operation_op set op_year=extract(year from op_date) where op_year is null and op_date is not null; -- 318 lines modified

--verifying stations
select * from wrbd.fish_fi where fi_st_id is null; --1974 and 1975 and 1976
select * from  wrbd.operation_op where op_year<1976; -- it is st2 as it is not marine institute data
update wrbd.fish_fi set fi_st

alter table wrbd.fish_fi  add constraint c_fk_fi_st_id FOREIGN KEY (fi_st_id) REFERENCES wrbd.station_st(st_id);
--La clé (fi_st_id)=(MI037) n'est pas présente dans la table « station_st ».
select * from wrbd.fish_fi where fi_st_id='MI037'; -- fi_id=24865
update wrbd.fish_fi set fi_st_id='MI0037' where fi_st_id='MI037'; --1100 lines modified
alter table wrbd.fish_fi  add constraint c_fk_fi_st_id FOREIGN KEY (fi_st_id) REFERENCES wrbd.station_st(st_id); -- OK

--verifying operations
select * from wrbd.fish_fi where fi_op_id is null; -- 23461 lines ouh!

select fi_id, op.* from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 22144



select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);
; -- 22086 -- export update_fish_op_1.sql --lancer ici le script sql


-- now I still have when joining on date and station some fish pointing to more than one operation
select * from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 58

-- only two choices there... One with 8 fishes (fi_id 10761 to 10768) one with 21 8 is 1674 21 is 1675
select * from wrbd.fish_fi where fi_id>=10761 and fi_id<=10768; -- to verify
update wrbd.fish_fi set fi_op_id=1674 where fi_id>=10761 and fi_id<=10768;

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 42

select * from wrbd.fish_fi where fi_id>=10769 and fi_id<=10789; -- to verify
update wrbd.fish_fi set fi_op_id=1675 where fi_id>=10769 and fi_id<=10789;

-- OK finished for pivot on date

select * from wrbd.fish_fi where fi_op_id is null; --1346 still.... some have dates uh ?