/* Biotics Update Patch 0001c 02/19/03. This incorporates changes to correct several conversion errors. Please refer to Test Track #2888 for the gory details. Contact Carol Fogelsong at 703 908 1864 or carol_fogelsong@natureserve.org if you have any questions regarding this patch. */ /* this is for #2888. This should be run for MI and BC programs only. All others were using a corrected conversion spreadsheet and probably don't need this fix. */ /* STEP #1: In the Biotics Tracker Query Builder, run this query. Depending on when your data was converted, you may get many, many records. Or you may get no records. If you get no records, you can go directly to Step #4. If you do get records, create a working list from the results. Give the working list a unique name. */ select plant_est.element_subnational_id, plant_ref.reference_code, plant_est.bcd_style_s_rank From (select est.element_subnational_id, est.distribution_reference_id, est.s_rank, est.bcd_style_s_rank from element_subnational est, scientific_name where est.sname_id = scientific_name.scientific_name_id and scientific_name.d_name_category_id = 4 and Est.s_rank = 'SNA') plant_est, (select reference_id, reference_code from reference where REFERENCE.reference_code = 'U96KAR03HQUS' or REFERENCE.reference_code like 'U97KAR%' OR REFERENCE.reference_code like 'U98KAR%' or REFERENCE.reference_code like 'U99KAR%' or REFERENCE.reference_code like 'B99KAR%' or REFERENCE.reference_code like 'U00KAR%' or REFERENCE.reference_code like 'U01KAR%' or REFERENCE.reference_code like 'U02KAR%') plant_ref, taxon_subnatl_dist tsd where plant_est.distribution_reference_id = plant_REF.reference_id and plant_est.element_subnational_id = tsd.element_subnational_id and tsd.d_origin_id = 3 and tsd.d_regularity_id =3 and tsd.d_dist_confidence_id = 2 and tsd.d_curr_presence_absence_id = 3 /* STEP #2: Get the id of the working list you just made. You can do this in Query Builder or SQL+ */ SELECT * FROM WORKING_LIST WHERE WORKING_LIST.WORKING_LIST_NAME = '?? Put the name of your working list here'; /* STEP #3: Edit the script below to put the working list id in the ???? fields. Now run the scripts in SQL+ to make the updates. */ update element_subnational set element_subnational.s_rank = 'SNR', element_subnational.rounded_s_rank = 'SNR', element_subnational.rec_last_mod_user = 'TestTrack2888' where element_subnational.element_subnational_id in (select element_subnational.element_subnational_id from element_subnational, working_list_element_data where working_list_element_data.working_list_id = ?????? and working_list_element_data.data_id = element_subnational.element_subnational_id); update taxon_subnatl_dist tsd set tsd.d_dist_confidence_id = 1, tsd.d_regularity_id = 1, rec_last_mod_user = 'TestTrack2888' where tsd.element_subnational_id in (select element_subnational.element_subnational_id from element_subnational, working_list_element_data where working_list_element_data.working_list_id = ?????? and working_list_element_data.data_id = element_subnational.element_subnational_id); /* Step #4 now do the same thing for N Ranks and N distribution ***********************/ In the Biotics Tracker Query Builder, run this query. Depending on when your data was converted, you may get many, many records. Or you may get no records. If you get no records, you can go directly to Step #7. If you do get records, create a working list from the results. Give the working list a unique name. */ select plant_ent.element_national_id, plant_ref.reference_code, plant_ent.bcd_style_n_rank From (select ent.element_national_id, ent.distribution_reference_id, ent.n_rank, ent.bcd_style_n_rank from element_national ent, scientific_name where ent.nname_id = scientific_name.scientific_name_id and scientific_name.d_name_category_id = 4 and Ent.n_rank = 'NNA') plant_ent, (select reference_id, reference_code from reference where REFERENCE.reference_code = 'U96KAR03HQUS' or REFERENCE.reference_code like 'U97KAR%' OR REFERENCE.reference_code like 'U98KAR%' or REFERENCE.reference_code like 'U99KAR%' or REFERENCE.reference_code like 'B99KAR%' or REFERENCE.reference_code like 'U00KAR%' or REFERENCE.reference_code like 'U01KAR%' or REFERENCE.reference_code like 'U02KAR%') plant_ref, taxon_natl_dist tnd where plant_ent.distribution_reference_id = plant_REF.reference_id and plant_ent.element_national_id = tnd.element_national_id and tnd.d_origin_id = 3 and tnd.d_regularity_id =3 and tnd.d_dist_confidence_id = 2 and tnd.d_curr_presence_absence_id = 3 /* STEP #5: Get the id of the working list you just made. You can do this in Query Builder or SQL+ */ SELECT * FROM WORKING_LIST WHERE WORKING_LIST.WORKING_LIST_NAME = '?? Put the name of your working list here'; /* STEP #6: Edit the script below to put the working list id in the ???? fields. Now run the scripts in SQL+ to make the updates. */ update element_national set element_national.n_rank = 'NNR', element_national.rounded_n_rank = 'NNR', element_national.rec_last_mod_user = 'TestTrack2888' where element_national.element_national_id in (select element_national.element_national_id from element_national, working_list_element_data where working_list_element_data.working_list_id = ?????? and working_list_element_data.data_id = element_national.element_national_id); update taxon_natl_dist tnd set tnd.d_dist_confidence_id = 1, tnd.d_regularity_id = 1, rec_last_mod_user = 'TestTrack2888' where tnd.element_national_id in (select element_national.element_national_id from element_national, working_list_element_data where working_list_element_data.working_list_id = ?????? and working_list_element_data.data_id = element_national.element_national_id); /* Step #7 ***************** update the db version tables *****************************/ INSERT INTO DB_VERSION (application_module, db_version) VALUES ('BIOTICST', 'Version 1.0001c'); INSERT INTO DB_UPGRADE_HISTORY (application_module, script_name, db_version) VALUES ('BIOTICST', 'biotics_update_0001c.sql', 'Version 1.0001c'); /************* commit the changes to the database *****************************/ commit;