...
Use SQL Developer to remove content not relevant to the current expedition.
2/13/2024: When removing records from LIMS.X_SAMPLE_DEPTH, do not remove any records with a scale_id=11311 or 11331. These records are needed for 999 and QAQC samples. If you already have, the script below will restore them.
| Code Block | ||||
|---|---|---|---|---|
| ||||
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
Select s.SAMPLE_NUMBER, '11311' AS SCALE_ID, 0 AS TOP, 0 AS BOT
from lims.sample s
LEFT OUTER JOIN lims.x_sample_depth sd
ON sd.sample_number = s.sample_number
and sd.scale_id = '11311'
where s.x_expedition = '999'
-- and s.x_site = 'U9999'
-- and s.x_hole = 'A'
-- and s.x_core = '1'
and sd.sample_number is null;
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
Select s.SAMPLE_NUMBER, '11331' AS SCALE_ID, 0 AS TOP, 0 AS BOT
from lims.sample s
LEFT OUTER JOIN lims.x_sample_depth sd
ON sd.sample_number = s.sample_number
and sd.scale_id = '11331'
where s.x_expedition = '999'
-- and s.x_site = 'U9999'
-- and s.x_hole = 'A'
-- and s.x_core = '1'
and sd.sample_number is null;
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
Select s.SAMPLE_NUMBER, '11311' AS SCALE_ID, 0 AS TOP, 0 AS BOT
from lims.sample s
LEFT OUTER JOIN lims.x_sample_depth sd
ON sd.sample_number = s.sample_number
and sd.scale_id = '11311'
where s.x_expedition = 'QAQC'
-- and s.x_site = 'U9999'
-- and s.x_hole = 'A'
-- and s.x_core = '1'
and sd.sample_number is null;
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
Select s.SAMPLE_NUMBER, '11331' AS SCALE_ID, 0 AS TOP, 0 AS BOT
from lims.sample s
LEFT OUTER JOIN lims.x_sample_depth sd
ON sd.sample_number = s.sample_number
and sd.scale_id = '11331'
where s.x_expedition = 'QAQC'
-- and s.x_site = 'U9999'
-- and s.x_hole = 'A'
-- and s.x_core = '1'
and sd.sample_number is null; |
Cleaning OVERVIEW Menu and Summary Data
...