qal_id = 0 should not be allowed for t_eel_stock

[elfunesto]

for NC data, we should_use qal_id = 1 => update the existing data and verify constraints in the db

[elfunesto]

to solve this: nc and np should not have a qal_id = 0 but qal_id = 1 in t_eelstock_eel => trigger in shiny: the function that check for duplicates and compare with the database should also check for qal_id 0

[elfunesto]

problem: in the db, all views (that are letter used by shiny to extract_data) only read qal_id in (1,2,4) so we would need modify all views if we want to be able to extract_data including qal_id=0. Moreover, some NC and NP already have eel_qal_id=1.

[elfunesto]

select * from datawg.t_eelstock_eel tee where eel_missvaluequal is not null and eel_qal_id in (1,2,4)

[elfunesto]

we decided to set qal_id=1 when NP or NC or NR, this raises a conflict for few cases: eel_year eel_emu_nameshort eel_lfs_code eel_hty_code eel_typ_id 1 2017 ES_Murc S C 4 2 2020 LT_total S T 4 3 2020 LT_total Y T 4 eel_year eel_emu_nameshort eel_lfs_code eel_hty_code eel_typ_id 1 2017 ES_Murc S C 4 2 2020 LT_total S T 4 3 2020 LT_total Y T 4 4 2013 ES_Vale YS T 6 5 2014 ES_Vale YS T 6 6 2015 ES_Vale YS T 6 7 2016 ES_Vale YS T 6 8 2017 ES_Vale YS T 6 9 2018 ES_Vale YS T 6

[elfunesto]

Just check, this should not even be missing data since the corresponding records has value, so we deprecate these missing values

[elfunesto]

This does the trick begin; -- deprecate wrong missing values that create duplicates update datawg.t_eelstock_eel set eel_qal_id =21 where eel_id in(521655,436466,436467,486606,486607,486608,486609,486610,486611);

--set qal_id = 1 for NP, NC, NR... update datawg.t_eelstock_eel set eel_qal_id =1 where eel_qal_id =0 and (eel_missvaluequal is not null);

--add a constraint to avoid having new duplicates ALTER TABLE datawg.t_eelstock_eel ADD CONSTRAINT ck_qal_id_and_missvalue CHECK ((eel_missvaluequal IS NULL) or (eel_qal_id != 0)); rollback;

[elfunesto]

script pasted in database_edition_2022