IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_INSERT_SK_LIGHT_IMP
Parameters
Name
Type
Mode
Definition
procedure PR_INSERT_SK_LIGHT_IMP -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS cursor c is select s.LUX ,s.CHROMATICITY_X ,s.CHROMATICITY_Y ,s.PLACEMENT ,s.EVENT_TIMESTAMP --already in TIMESTAMP(6) WITH TIME ZONE ,s.DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from SK_LIGHT_int s order by s.PARTICIPANT_ID asc, s.DEVICE_TYPE, s.EVENT_TIMESTAMP asc ; tmp_PARTICIPANT_ID varchar2(15) := 'x'; tmp_DEVICE_TYPE varchar2(15) := 'x'; pre_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE := NULL; cnt_PARTICIPANT pls_integer := 0; seq_interval pls_integer := 200; lower_seq pls_integer := 1; upper_seq pls_integer := 200; begin -------------------------------------------------------- -- Data insertion for Table SK_LIGHT -------------------------------------------------------- --EXECUTE IMMEDIATE 'truncate table SK_LIGHT_int'; --insert daily new records with EVENT_TIMESTAMP data conversion into an intermediate table first --for handling the different time zone timestamps consistently EXECUTE IMMEDIATE 'truncate table SK_LIGHT_PARTICIPANTs'; EXECUTE IMMEDIATE 'truncate table SK_LIGHT_int'; EXECUTE IMMEDIATE 'truncate table SK_LIGHT_test'; insert into SK_LIGHT_PARTICIPANTs ( ID_SEQ ,PARTICIPANT_ID) with qq as ( select distinct s.PARTICIPANT_ID from stg_SK_LIGHT s join STUDYPARTICIPANTS p on s.PARTICIPANT_ID = p.PARTICIPANTIDENTIFIER order by s.PARTICIPANT_ID asc ) select rownum, PARTICIPANT_ID from qq order by PARTICIPANT_ID asc ; commit; select count(*) into cnt_PARTICIPANT from SK_LIGHT_PARTICIPANTs; for i in 1 .. CEIL(cnt_PARTICIPANT/seq_interval) loop lower_seq := (i-1) * seq_interval + 1; upper_seq := i * seq_interval; insert into SK_LIGHT_int ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) select s.LUX ,s.CHROMATICITY_X ,s.CHROMATICITY_Y ,l.category_val as PLACEMENT ,TO_UTC_TIMESTAMP_TZ(s.EVENT_TIMESTAMP) as EVENT_TIMESTAMP ,d.category_val as DEVICE_TYPE ,s.PARTICIPANT_ID --,s.DEVICE_ID from stg_SK_LIGHT s join SK_LIGHT_PARTICIPANTs p on s.PARTICIPANT_ID = p.PARTICIPANT_ID join SK_lookup d on d.data_type = 'DEVICE_TYPE' and lower(d.category_txt) = lower(s.DEVICE_TYPE) join SK_lookup l on l.data_type = 'LIGHT_SENSOR_PLACEMENT' and lower(l.category_txt) = lower(s.PLACEMENT) where p.ID_SEQ between lower_seq and upper_seq ; commit; end loop; for rec in c loop if ( (tmp_PARTICIPANT_ID <> rec.PARTICIPANT_ID) or (tmp_DEVICE_TYPE <> rec.DEVICE_TYPE) ) then --commit for the previous PARTICIPANT_ID and DEVICE_TYPE commit; --insert first record for a new PARTICIPANT_ID and/or DEVICE_TYPE insert into SK_LIGHT_test ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) values ( rec.LUX ,rec.CHROMATICITY_X ,rec.CHROMATICITY_Y ,rec.PLACEMENT ,rec.EVENT_TIMESTAMP ,rec.DEVICE_TYPE ,rec.PARTICIPANT_ID --,rec.DEVICE_ID ); --track the PARTICIPANT_ID, DEVICE_TYPE, and TIMESTAMP tmp_PARTICIPANT_ID := rec.PARTICIPANT_ID; tmp_DEVICE_TYPE := rec.DEVICE_TYPE; pre_TIMESTAMP := rec.EVENT_TIMESTAMP; elsif rec.EVENT_TIMESTAMP - pre_TIMESTAMP >= '+00 00:10:00.000000' then --record at every 10 minutes insert into SK_LIGHT_test ( LUX ,CHROMATICITY_X ,CHROMATICITY_Y ,PLACEMENT ,EVENT_TIMESTAMP ,DEVICE_TYPE ,PARTICIPANT_ID --,DEVICE_ID ) values ( rec.LUX ,rec.CHROMATICITY_X ,rec.CHROMATICITY_Y ,rec.PLACEMENT ,rec.EVENT_TIMESTAMP ,rec.DEVICE_TYPE ,rec.PARTICIPANT_ID --,rec.DEVICE_ID ); --reset pre_TIMESTAMP to current EVENT_TIMESTAMP pre_TIMESTAMP := rec.EVENT_TIMESTAMP; end if; end loop; commit; end;