IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_UPDATE_LOAD_LOG
Parameters
Name
Type
Mode
Definition
procedure pr_update_LOAD_LOG /*------------------------------------------------------ Project: Intern Health Study 2023 (Care Evolution Data) Description: Oracle procedure pr_populate_IHS_data Author: Siqing Hu (shu@med.umich.edu) Created: 2023-03-01 Updated: 2023-12-05 ------------------------------------------------------*/ -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS begin -------------------------------------------------------- -- 0 - insert staging data counts into Table LOAD_LOG -------------------------------------------------------- insert into load_log ( LOAD_DATE ,CNT_AUXILIARYDATA --,CNT_COMPETITIONS --no COMPETITIONS data yet ,CNT_FITBITACTIVITYLOGS ,CNT_FITBITDAILYDATA ,CNT_FITBITDEVICES ,CNT_FITBITRESTINGHEARTRATES ,CNT_FITBITSLEEPLOGDETAILS ,CNT_FITBITSLEEPLOGS --,CNT_FITBIT_HEARTRATE --,CNT_FITBIT_STEPS ,CNT_HEALTHKITACTIVITYSUMMARIES ,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME ,CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED ,CNT_HEALTHKITSAMPLES_HEARTRATE ,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY ,CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED ,CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED ,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE ,CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED ,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL ,CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED ,CNT_HEALTHKITSAMPLES_STEPS ,CNT_HEALTHKITSAMPLES_STEPS_DELETED ,CNT_HEALTHKITSTATISTICS_DAILYSTEPS ,CNT_MANIFEST --,CNT_NOTIFICATIONS --no longer in IHS 2023 ,CNT_STUDYPARTICIPANTS ,CNT_SURVEYCONSENTSIGNATURERESULTS ,CNT_SURVEYDEFINITIONS ,CNT_SURVEYQUESTIONRESULTS ,CNT_SURVEYRESULTS ,CNT_SURVEYSTEPRESULTS ,CNT_SURVEYTASKS --added on 4/5/2022: ,CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT ,CNT_ANALYTICSEVENTS_NOTIFICATIONSENT ,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED ,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED ,CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT ,CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED --added on 6/13/2022: ,CNT_ANALYTICSEVENTS_EMAILSEND ,CNT_ANALYTICSEVENTS_EMAILDELIVERY ,CNT_ANALYTICSEVENTS_EMAILOPEN ,CNT_ANALYTICSEVENTS_EMAILCLICK ,CNT_ANALYTICSEVENTS_EMAILBOUNCE ,CNT_ANALYTICSEVENTS_EMAILCOMPLAINT --added on 7/1/2022: ,CNT_HEALTHKITSTATISTICS_HOURLYSTEPS --added for IHS 2023: --,CNT_SURVEYDICTIONARY ,CNT_GARMINACTIVITYSUMMARY ,CNT_GARMINDAILYSUMMARY ,CNT_GARMINDAILYSUMMARY_SAMPLES ,CNT_GARMINEPOCHSUMMARY ,CNT_GARMINHRVSUMMARY ,CNT_GARMINHRVSUMMARY_SAMPLES ,CNT_GARMINMOVEIQACTIVITYSUMMARY ,CNT_GARMINSLEEPSUMMARY ,CNT_GARMINSLEEPSUMMARY_DURATION ,CNT_GARMINSLEEPSUMMARY_SCORE ,CNT_GARMINSTRESSDETAILSUMMARY ,CNT_GARMINSTRESSDETAILSUMMARY_SAMPLES ,CNT_GARMINTHIRDPARTYDAILYSUMMARY ,CNT_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES ,CNT_GARMINUSERMETRICSSUMMARY ,CNT_SK_VISITS ,CNT_SK_LIGHT ,CNT_SK_USAGE ,CNT_SK_KEYBOARD --added on 12/5/2023: ,CNT_PROJECTDEVICEDATA ) select sysdate load_date ,(select count(*) from STG_AUXILIARYDATA) CNT_AUXILIARYDATA --,(select count(*) from STG_COMPETITIONS) CNT_COMPETITIONS --no COMPETITIONS data yet ,(select count(*) from STG_FITBITACTIVITYLOGS) CNT_FITBITACTIVITYLOGS ,(select count(*) from STG_FITBITDAILYDATA) CNT_FITBITDAILYDATA ,(select count(*) from STG_FITBITDEVICES) CNT_FITBITDEVICES ,(select count(*) from STG_FITBITRESTINGHEARTRATES) CNT_FITBITRESTINGHEARTRATES ,(select count(*) from STG_FITBITSLEEPLOGDETAILS) CNT_FITBITSLEEPLOGDETAILS ,(select count(*) from STG_FITBITSLEEPLOGS) CNT_FITBITSLEEPLOGS --,(select count(*) from STG_FITBIT_HEARTRATE) CNT_FITBIT_HEARTRATE --,(select count(*) from STG_FITBIT_STEPS) CNT_FITBIT_STEPS ,(select count(*) from STG_HEALTHKITACTIVITYSUMMARIES) CNT_HEALTHKITACTIVITYSUMMARIES ,(select count(*) from STG_HEALTHKITSAMPLES_APPLEEXERCISETIME) CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME ,(select count(*) from STG_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED) CNT_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED ,(select count(*) from STG_HEALTHKITSAMPLES_HEARTRATE) CNT_HEALTHKITSAMPLES_HEARTRATE ,(select count(*) from STG_HEALTHKITSAMPLES_HEARTRATEVARIABILITY) CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY ,(select count(*) from STG_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED) CNT_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED ,(select count(*) from STG_HEALTHKITSAMPLES_HEARTRATE_DELETED) CNT_HEALTHKITSAMPLES_HEARTRATE_DELETED ,(select count(*) from STG_HEALTHKITSAMPLES_RESTINGHEARTRATE) CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE ,(select count(*) from STG_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED) CNT_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED ,(select count(*) from STG_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL) CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL ,(select count(*) from STG_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED) CNT_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED ,(select count(*) from STG_HEALTHKITSAMPLES_STEPS) CNT_HEALTHKITSAMPLES_STEPS ,(select count(*) from STG_HEALTHKITSAMPLES_STEPS_DELETED) CNT_HEALTHKITSAMPLES_STEPS_DELETED ,(select count(*) from STG_HEALTHKITSTATISTICS_DAILYSTEPS) CNT_HEALTHKITSTATISTICS_DAILYSTEPS ,(select count(*) from STG_MANIFEST) CNT_MANIFEST --,(select count(*) from STG_NOTIFICATIONS) CNT_NOTIFICATIONS --no longer in IHS 2023 ,(select count(*) from STG_STUDYPARTICIPANTS) CNT_STUDYPARTICIPANTS ,(select count(*) from STG_SURVEYCONSENTSIGNATURERESULTS) CNT_SURVEYCONSENTSIGNATURERESULTS ,(select count(*) from STG_SURVEYDEFINITIONS) CNT_SURVEYDEFINITIONS ,(select count(*) from STG_SURVEYQUESTIONRESULTS) CNT_SURVEYQUESTIONRESULTS ,(select count(*) from STG_SURVEYRESULTS) CNT_SURVEYRESULTS ,(select count(*) from STG_SURVEYSTEPRESULTS) CNT_SURVEYSTEPRESULTS ,(select count(*) from STG_SURVEYTASKS) CNT_SURVEYTASKS --added on 4/5/2022: ,(select count(*) from STG_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT) CNT_ANALYTICSEVENTS_EMAILNOTIFICATIONSENT ,(select count(*) from STG_ANALYTICSEVENTS_NOTIFICATIONSENT) CNT_ANALYTICSEVENTS_NOTIFICATIONSENT ,(select count(*) from STG_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED) CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED ,(select count(*) from STG_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED) CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED ,(select count(*) from STG_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT) CNT_ANALYTICSEVENTS_PUSHNOTIFICATIONSENT ,(select count(*) from STG_ANALYTICSEVENTS_SURVEYSTEPVIEWED) CNT_ANALYTICSEVENTS_SURVEYSTEPVIEWED --added on 6/13/2022: ,(select count(*) from STG_ANALYTICSEVENTS_EMAILSEND) CNT_ANALYTICSEVENTS_EMAILSEND ,(select count(*) from STG_ANALYTICSEVENTS_EMAILDELIVERY) CNT_ANALYTICSEVENTS_EMAILDELIVERY ,(select count(*) from STG_ANALYTICSEVENTS_EMAILOPEN) CNT_ANALYTICSEVENTS_EMAILOPEN ,(select count(*) from STG_ANALYTICSEVENTS_EMAILCLICK) CNT_ANALYTICSEVENTS_EMAILCLICK ,(select count(*) from STG_ANALYTICSEVENTS_EMAILBOUNCE) CNT_ANALYTICSEVENTS_EMAILBOUNCE ,(select count(*) from STG_ANALYTICSEVENTS_EMAILCOMPLAINT) CNT_ANALYTICSEVENTS_EMAILCOMPLAINT --added on 7/1/2022: ,(select count(*) from STG_HEALTHKITSTATISTICS_HOURLYSTEPS) CNT_HEALTHKITSTATISTICS_HOURLYSTEPS --added for IHS 2023: --,(select count(*) from STG_SURVEYDICTIONARY) CNT_SURVEYDICTIONARY ,(select count(*) from STG_GARMINACTIVITYSUMMARY) CNT_GARMINACTIVITYSUMMARY ,(select count(*) from STG_GARMINDAILYSUMMARY) CNT_GARMINDAILYSUMMARY ,(select count(*) from STG_GARMINDAILYSUMMARY_SAMPLES) CNT_GARMINDAILYSUMMARY_SAMPLES ,(select count(*) from STG_GARMINEPOCHSUMMARY) CNT_GARMINEPOCHSUMMARY ,(select count(*) from STG_GARMINHRVSUMMARY) CNT_GARMINHRVSUMMARY ,(select count(*) from STG_GARMINHRVSUMMARY_SAMPLES) CNT_GARMINHRVSUMMARY_SAMPLES ,(select count(*) from STG_GARMINMOVEIQACTIVITYSUMMARY) CNT_GARMINMOVEIQACTIVITYSUMMARY ,(select count(*) from STG_GARMINSLEEPSUMMARY) CNT_GARMINSLEEPSUMMARY ,(select count(*) from STG_GARMINSLEEPSUMMARY_DURATION) CNT_GARMINSLEEPSUMMARY_DURATION ,(select count(*) from STG_GARMINSLEEPSUMMARY_SCORE) CNT_GARMINSLEEPSUMMARY_SCORE ,(select count(*) from STG_GARMINSTRESSDETAILSUMMARY) CNT_GARMINSTRESSDETAILSUMMARY ,(select count(*) from STG_GARMINSTRESSDETAILSUMMARY_SAMPLES) CNT_GARMINSTRESSDETAILSUMMARY_SAMPLES ,(select count(*) from STG_GARMINTHIRDPARTYDAILYSUMMARY) CNT_GARMINTHIRDPARTYDAILYSUMMARY ,(select count(*) from STG_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES) CNT_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES ,(select count(*) from STG_GARMINUSERMETRICSSUMMARY) CNT_GARMINUSERMETRICSSUMMARY ,(select count(*) from STG_SK_VISITS) CNT_SK_VISITS ,(select count(*) from STG_SK_LIGHT) CNT_SK_LIGHT ,(select count(*) from STG_SK_USAGE) CNT_SK_USAGE ,(select count(*) from STG_SK_KEYBOARD) CNT_SK_KEYBOARD --added on 12/5/2023: ,(select count(*) from STG_PROJECTDEVICEDATA) CNT_PROJECTDEVICEDATA from dual ; commit; -------------------------------------------------------- -- 19 - Data insertion for Table MANIFEST -------------------------------------------------------- insert into MANIFEST ( EXPORTSTARTDATE ,EXPORTENDDATE ,EXPORTCONFIGURATION ,EXCLUDEDPARTICIPANTIDENTIFIERS ,AUXILIARYDATA --,COMPETITIONS --no COMPETITIONS data yet (modified on 3/30/2022) ,FITBITACTIVITYLOGS ,FITBITDAILYDATA ,FITBITDEVICES ,FITBITRESTINGHEARTRATES ,FITBITSLEEPLOGDETAILS ,FITBITSLEEPLOGS ,HEALTHKITACTIVITYSUMMARIES ,HEALTHKITSAMPLES_APPLEEXERCISETIME ,HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED ,HEALTHKITSAMPLES_HEARTRATE ,HEALTHKITSAMPLES_HEARTRATEVARIABILITY ,HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED ,HEALTHKITSAMPLES_HEARTRATE_DELETED ,HEALTHKITSAMPLES_RESTINGHEARTRATE ,HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED ,HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL ,HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED ,HEALTHKITSAMPLES_STEPS ,HEALTHKITSAMPLES_STEPS_DELETED ,HEALTHKITSTATISTICS_DAILYSTEPS --,NOTIFICATIONS --no longer in IHS 2023 ,STUDYPARTICIPANTS ,SURVEYCONSENTSIGNATURERESULTS ,SURVEYDEFINITIONS ,SURVEYQUESTIONRESULTS ,SURVEYRESULTS ,SURVEYSTEPRESULTS ,SURVEYTASKS ,FITBIT_INTRADAY_ACTIVITIES_HEART_FILE ,FITBIT_INTRADAY_ACTIVITIES_ELEVATION_FILE ,FITBIT_INTRADAY_ACTIVITIES_DISTANCE_FILE ,FITBIT_INTRADAY_ACTIVITIES_FLOORS_FILE ,FITBIT_INTRADAY_ACTIVITIES_STEPS_FILE ,FITBIT_INTRADAY_ACTIVITIES_CALORIES_FILE --added on 4/5/2022: ,ANALYTICSEVENTS_EMAILNOTIFICATIONSENT ,ANALYTICSEVENTS_NOTIFICATIONSENT ,ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED ,ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED ,ANALYTICSEVENTS_PUSHNOTIFICATIONSENT ,ANALYTICSEVENTS_SURVEYSTEPVIEWED --add on 6/13/2022: ,ANALYTICSEVENTS_EMAILSEND ,ANALYTICSEVENTS_EMAILDELIVERY ,ANALYTICSEVENTS_EMAILOPEN ,ANALYTICSEVENTS_EMAILCLICK ,ANALYTICSEVENTS_EMAILBOUNCE ,ANALYTICSEVENTS_EMAILCOMPLAINT --added on 7/1/2022: ,HEALTHKITSTATISTICS_HOURLYSTEPS --added on 11/2/2022: ,FITBIT_INTRADAY_HRV_FILE --added for IHS 2023: ,SurveyDictionary ,GarminActivitySummary ,GarminDailySummary ,GarminDailySummary_Samples ,GarminEpochSummary ,GarminHrvSummary ,GarminHrvSummary_Samples ,GarminMoveIQActivitySummary ,GarminSleepSummary ,GarminSleepSummary_Duration ,GarminSleepSummary_Score ,GarminStressDetailSummary ,GarminStressDetailSummary_Samples ,GarminThirdPartyDailySummary ,GarminThirdPartyDailySummary_Samples ,GarminUserMetricsSummary ,SK_light_IP ,SK_VISITS_IP ,SK_KEYBOARD_IP ,SK_USAGE_messages_IP ,SK_USAGE_phone_IP ,SK_light_AW ,SK_USAGE_messages_AW ,SK_USAGE_phone_AW --added on 12/5/2023: ,PROJECTDEVICEDATA ) select TO_UTC_TIMESTAMP_TZ(s.EXPORTSTARTDATE) as EXPORTSTARTDATE ,TO_UTC_TIMESTAMP_TZ(s.EXPORTENDDATE) as EXPORTENDDATE ,s.EXPORTCONFIGURATION ,s.EXCLUDEDPARTICIPANTIDENTIFIERS ,s.AUXILIARYDATA --,s.COMPETITIONS --no COMPETITIONS data yet (modified on 3/30/2022) ,s.FITBITACTIVITYLOGS ,s.FITBITDAILYDATA ,s.FITBITDEVICES ,s.FITBITRESTINGHEARTRATES ,s.FITBITSLEEPLOGDETAILS ,s.FITBITSLEEPLOGS ,s.HEALTHKITV2ACTIVITYSUMMARIES as HEALTHKITACTIVITYSUMMARIES ,s.HEALTHKITV2SAMPLES_APPLEEXERCISETIME as HEALTHKITSAMPLES_APPLEEXERCISETIME ,s.HEALTHKITV2SAMPLES_APPLEEXERCISETIME_DELETED as HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED ,s.HEALTHKITV2SAMPLES_HEARTRATE as HEALTHKITSAMPLES_HEARTRATE ,s.HEALTHKITV2SAMPLES_HEARTRATEVARIABILITY as HEALTHKITSAMPLES_HEARTRATEVARIABILITY ,s.HEALTHKITV2SAMPLES_HEARTRATEVARIABILITY_DELETED as HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED ,s.HEALTHKITV2SAMPLES_HEARTRATE_DELETED as HEALTHKITSAMPLES_HEARTRATE_DELETED ,s.HEALTHKITV2SAMPLES_RESTINGHEARTRATE as HEALTHKITSAMPLES_RESTINGHEARTRATE ,s.HEALTHKITV2SAMPLES_RESTINGHEARTRATE_DELETED as HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED ,s.HEALTHKITV2SAMPLES_SLEEPANALYSISINTERVAL as HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL ,s.HEALTHKITV2SAMPLES_SLEEPANALYSISINTERVAL_DELETED as HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED ,s.HEALTHKITV2SAMPLES_STEPS as HEALTHKITSAMPLES_STEPS ,s.HEALTHKITV2SAMPLES_STEPS_DELETED as HEALTHKITSAMPLES_STEPS_DELETED ,s.HEALTHKITV2STATISTICS_DAILYSTEPS as HEALTHKITSTATISTICS_DAILYSTEPS --,s.NOTIFICATIONS --no longer in IHS 2023 ,s.ALLPARTICIPANTS as STUDYPARTICIPANTS ,s.SURVEYCONSENTSIGNATURERESULTS ,s.SURVEYDEFINITIONS ,s.SURVEYQUESTIONRESULTS ,s.SURVEYRESULTS ,s.SURVEYSTEPRESULTS ,s.SURVEYTASKS ,s.FITBIT_INTRADAY_ACTIVITIES_HEART_FILE ,s.FITBIT_INTRADAY_ACTIVITIES_ELEVATION_FILE ,s.FITBIT_INTRADAY_ACTIVITIES_DISTANCE_FILE ,s.FITBIT_INTRADAY_ACTIVITIES_FLOORS_FILE ,s.FITBIT_INTRADAY_ACTIVITIES_STEPS_FILE ,s.FITBIT_INTRADAY_ACTIVITIES_CALORIES_FILE --added on 4/5/2022: ,s.ANALYTICSEVENTS_EMAILNOTIFICATIONSENT ,s.ANALYTICSEVENTS_NOTIFICATIONSENT ,s.ANALYTICSEVENTS_PUSHNOTIFICATIONOPENED ,s.ANALYTICSEVENTS_PUSHNOTIFICATIONRECEIVED ,s.ANALYTICSEVENTS_PUSHNOTIFICATIONSENT ,s.ANALYTICSEVENTS_SURVEYSTEPVIEWED --added on 6/13/2022: ,s.ANALYTICSEVENTS_EMAILSEND ,s.ANALYTICSEVENTS_EMAILDELIVERY ,s.ANALYTICSEVENTS_EMAILOPEN ,s.ANALYTICSEVENTS_EMAILCLICK ,s.ANALYTICSEVENTS_EMAILBOUNCE ,s.ANALYTICSEVENTS_EMAILCOMPLAINT --added on 7/1/2022: ,s.HEALTHKITV2STATISTICS_HOURLYSTEPS as HEALTHKITSTATISTICS_HOURLYSTEPS --added on 11/2/2022: ,s.FITBIT_INTRADAY_HRV_FILE --added for IHS 2023: ,s.SurveyDictionary ,s.GarminActivitySummary ,s.GarminDailySummary ,s.GarminDailySummary_Samples ,s.GarminEpochSummary ,s.GarminHrvSummary ,s.GarminHrvSummary_Samples ,s.GarminMoveIQActivitySummary ,s.GarminSleepSummary ,s.GarminSleepSummary_Duration ,s.GarminSleepSummary_Score ,s.GarminStressDetailSummary ,s.GarminStressDetailSummary_Samples ,s.GarminThirdPartyDailySummary ,s.GarminThirdPartyDailySummary_Samples ,s.GarminUserMetricsSummary ,s.SK_light_IP ,s.SK_VISITS_IP ,s.SK_KEYBOARD_IP ,s.SK_USAGE_messages_IP ,s.SK_USAGE_phone_IP ,s.SK_light_AW ,s.SK_USAGE_messages_AW ,s.SK_USAGE_phone_AW --added on 12/5/2023: ,s.PROJECTDEVICEDATA from stg_MANIFEST s --left join MANIFEST o on TO_UTC_TIMESTAMP_TZ(s.EXPORTSTARTDATE) = o.EXPORTSTARTDATE --where o.rowid is null ; commit; /* -------------------------------------------------------- -- 20 - Data insertion for Table COMPETITIONS -------------------------------------------------------- insert into competitions ( COMPETITIONKEY ,COMPETITION_ID ,TEAM1_INSTITUTION ,TEAM1_PROGRAM ,TEAM2_INSTITUTION ,TEAM2_PROGRAM ,CALCULATION_DATE ,CALCULATION_TIMESTAMP ,TEAM1_SCORE ,TEAM2_SCORE ) select COMPETITIONKEY ,COMPETITION_ID ,TEAM1_INSTITUTION ,TEAM1_PROGRAM ,TEAM2_INSTITUTION ,TEAM2_PROGRAM ,to_date(CALCULATION_DATE, 'yyyy-mm-dd') as CALCULATION_DATE ,TO_UTC_TIMESTAMP_TZ(CALCULATION_TIMESTAMP) as CALCULATION_TIMESTAMP ,TEAM1_SCORE ,TEAM2_SCORE from stg_competitions ; commit; */ end;