C:\Temp\openclinica_sqldatamart-master>set start_time=14:37:26.78 C:\Temp\openclinica_sqldatamart-master>set "psql=C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" C:\Temp\openclinica_sqldatamart-master>set PGHOST=127.0.0.1 C:\Temp\openclinica_sqldatamart-master>set PGPORT=5432 C:\Temp\openclinica_sqldatamart-master>set PGUSER=postgres C:\Temp\openclinica_sqldatamart-master>set PGPASSWORD=password C:\Temp\openclinica_sqldatamart-master>set foreign_server_host_name=localhost C:\Temp\openclinica_sqldatamart-master>set foreign_server_host_address=127.0.0.1 C:\Temp\openclinica_sqldatamart-master>set foreign_server_port=5433 C:\Temp\openclinica_sqldatamart-master>set foreign_server_database=openclinica C:\Temp\openclinica_sqldatamart-master>set foreign_server_user_password=password C:\Temp\openclinica_sqldatamart-master>set foreign_server_openclinica_schema_name=public C:\Temp\openclinica_sqldatamart-master>set "scripts_path=C:\Temp\openclinica_sqldatamart-master" C:\Temp\openclinica_sqldatamart-master>"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" -q -d postgres -c "CREATE DATABASE openclinica_fdw_db;" -P pager C:\Temp\openclinica_sqldatamart-master>"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" -q -d openclinica_fdw_db -c "CREATE SCHEMA openclinica_fdw;" -P pager C:\Temp\openclinica_sqldatamart-master>"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" -q -d openclinica_fdw_db -c "ALTER DATABASE openclinica_fdw_db SET search_path = 'openclinica_fdw';" C:\Temp\openclinica_sqldatamart-master>"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" -q -d openclinica_fdw_db -f "C:\Temp\openclinica_sqldatamart-master"\dm_functions.sql -P pager C:\Temp\openclinica_sqldatamart-master>"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql" -q -d openclinica_fdw_db -f "C:\Temp\openclinica_sqldatamart-master"\dm_build_commands.sql -v foreign_server_host_name='localhost' -v foreign_server_host_address='127.0.0.1' -v foreign_server_port='5433' -v foreign_server_database='openclinica' -v foreign_server_user_password='password' -v foreign_server_openclinica_schema_name='public' -P pager psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:6: ERROR: role "dm_admin" already exists psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:10: NOTICE: role "postgres" is already a member of role "dm_admin" dm_create_ft_catalog ---------------------- (1 row) dm_create_ft_openclinica -------------------------- (1 row) dm_create_ft_openclinica_matviews ----------------------------------- (1 row) dm_create_ft_openclinica_matview_indexes ------------------------------------------ (1 row) dm_create_dm_response_sets ---------------------------- (1 row) dm_create_dm_metadata ----------------------- (1 row) dm_create_dm_metadata_event_crf_ig ------------------------------------ (1 row) dm_create_dm_metadata_crf_ig_item ----------------------------------- (1 row) dm_create_dm_metadata_study ----------------------------- (1 row) psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:69: ERROR: syntax error at or near "SELECT" LINE 4: SELECT dm_create_dm_clinicaldata(); ^ psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:74: ERROR: relation "dm.clinicaldata" does not exist dm_create_dm_subjects ----------------------- (1 row) dm_create_dm_subject_event_crf_status --------------------------------------- (1 row) psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:80: ERROR: relation "dm.clinicaldata" does not exist LINE 18: dm.clinicaldata ^ QUERY: CREATE MATERIALIZED VIEW dm.subject_event_crf_expected AS SELECT s.study_name, s.site_oid, s.subject_id, e.event_oid, e.crf_parent_name FROM ( SELECT DISTINCT clinicaldata.study_name, clinicaldata.site_oid, clinicaldata.site_name, clinicaldata.subject_id FROM dm.clinicaldata ) AS s, ( SELECT DISTINCT metadata.study_name, metadata.event_oid, metadata.crf_parent_name FROM dm.metadata ) AS e WHERE s.study_name = e.study_name CONTEXT: PL/pgSQL function dm_create_dm_subject_event_crf_expected() line 3 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:82: ERROR: relation "dm.subject_event_crf_expected" does not exist LINE 53: dm.subject_event_crf_expected AS e ^ QUERY: CREATE MATERIALIZED VIEW dm.subject_event_crf_join AS SELECT e.study_name, e.site_oid, s.site_name, s.subject_person_id, s.subject_oid, e.subject_id, s.study_subject_id, s.subject_secondary_label, s.subject_date_of_birth, s.subject_sex, s.subject_enrol_date, s.person_id, s.subject_owned_by_user, s.subject_last_updated_by_user, e.event_oid, s.event_order, s.event_name, s.event_repeat, s.event_start, s.event_end, CASE WHEN s.event_status IS NOT NULL THEN s.event_status ELSE $$not scheduled$$ END AS event_status, s.event_owned_by_user, s.event_last_updated_by_user, s.crf_parent_oid, e.crf_parent_name, s.crf_version, s.crf_version_oid, s.crf_is_required, s.crf_is_double_entry, s.crf_is_hidden, s.crf_null_values, s.crf_date_created, s.crf_last_update, s.crf_date_completed, s.crf_date_validate, s.crf_date_validate_completed, s.crf_owned_by_user, s.crf_last_updated_by_user, s.crf_status, s.crf_validated_by_user, s.crf_sdv_status, s.crf_sdv_status_last_updated, s.crf_sdv_by_user, s.crf_interviewer_name, s.crf_interview_date FROM dm.subject_event_crf_expected AS e LEFT JOIN dm.subject_event_crf_status AS s ON s.subject_id = e.subject_id AND s.event_oid = e.event_oid AND s.crf_parent_name = e.crf_parent_name CONTEXT: PL/pgSQL function dm_create_dm_subject_event_crf_join() line 3 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:84: ERROR: relation "dm.clinicaldata" does not exist LINE 69: dm.clinicaldata AS c... ^ QUERY: CREATE MATERIALIZED VIEW dm.discrepancy_notes_all AS SELECT dn_src.discrepancy_note_id, dn_src.study_name, dn_src.site_name, dn_src.subject_id, dn_src.event_name, dn_src.crf_parent_name, dn_src.crf_section_label, dn_src.item_description, dn_src.column_name, dn_src.parent_dn_id, dn_src.entity_type, dn_src.description, dn_src.detailed_notes, dn_src.date_created, dn_src.discrepancy_note_type, dn_src.resolution_status, dn_src.discrepancy_note_owner FROM ( SELECT DISTINCT ON (sua.discrepancy_note_id) sua.discrepancy_note_id, sua.study_name, sua.site_name, sua.subject_id, sua.event_name, sua.crf_parent_name, sua.crf_section_label, sua.item_description, sua.column_name, dn.parent_dn_id, dn.entity_type, dn.description, dn.detailed_notes, dn.date_created, CASE WHEN dn.discrepancy_note_type_id = 1 THEN $$Failed Validation Check$$ :: TEXT WHEN dn.discrepancy_note_type_id = 2 THEN $$Annotation$$ :: TEXT WHEN dn.discrepancy_note_type_id = 3 THEN $$Query$$ :: TEXT WHEN dn.discrepancy_note_type_id = 4 THEN $$Reason for Change$$ :: TEXT ELSE $$unhandled$$ :: TEXT END :: TEXT AS discrepancy_note_type, rs.name AS resolution_status, ua.user_name AS discrepancy_note_owner FROM ( ( ( ( SELECT didm.discrepancy_note_id, didm.column_name, cd.study_name, cd.site_name, cd.subject_id, cd.event_name, cd.crf_parent_name, cd.crf_section_label, cd.item_description FROM openclinica_fdw.dn_item_data_map AS didm JOIN dm.clinicaldata AS cd ON cd.item_data_id = didm.item_data_id UNION ALL SELECT decm.discrepancy_note_id, decm.column_name, cd.study_name, cd.site_name, cd.subject_id, cd.event_name, cd.crf_parent_name, NULL :: TEXT AS crf_section_label, NULL :: TEXT AS item_description FROM openclinica_fdw.dn_event_crf_map AS decm JOIN dm.clinicaldata AS cd ON cd.event_crf_id = decm.event_crf_id ) UNION ALL SELECT dsem.discrepancy_note_id, dsem.column_name, cd.study_name, cd.site_name, cd.subject_id, cd.event_name, NULL :: TEXT AS crf_parent_name, NULL :: TEXT AS crf_section_label, NULL :: TEXT AS item_description FROM openclinica_fdw.dn_study_event_map AS dsem JOIN dm.clinicaldata AS cd ON cd.study_event_id = dsem.study_event_id ) UNION ALL SELECT dssm.discrepancy_note_id, dssm.column_name, cd.study_name, cd.site_name, cd.subject_id, NULL :: TEXT AS event_name, NULL :: TEXT AS crf_parent_name, NULL :: TEXT AS crf_section_label, NULL :: TEXT AS item_description FROM openclinica_fdw.dn_study_subject_map AS dssm JOIN dm.clinicaldata AS cd ON cd.study_subject_id = dssm.study_subject_id ) UNION ALL SELECT dsm.discrepancy_note_id, dsm.column_name, cd.study_name, cd.site_name, cd.subject_id, NULL :: TEXT AS event_name, NULL :: TEXT AS crf_parent_name, NULL :: TEXT AS crf_section_label, NULL :: TEXT AS item_description FROM openclinica_fdw.dn_subject_map AS dsm JOIN dm.clinicaldata AS cd ON cd.subject_id_seq = dsm.subject_id ) AS sua JOIN openclinica_fdw.discrepancy_note AS dn ON dn.discrepancy_note_id = sua.discrepancy_note_id JOIN openclinica_fdw.resolution_status AS rs ON rs.resolution_status_id = dn.resolution_status_id JOIN openclinica_fdw.user_account AS ua ON ua.user_id = dn.owner_id ) AS dn_src; CONTEXT: PL/pgSQL function dm_create_dm_discrepancy_notes_all() line 3 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:86: ERROR: relation "dm.discrepancy_notes_all" does not exist LINE 64: dm.discrepancy_notes_all AS sub ^ QUERY: CREATE MATERIALIZED VIEW dm.discrepancy_notes_parent AS SELECT sub.discrepancy_note_id, sub.study_name, sub.site_name, sub.subject_id, sub.event_name, sub.crf_parent_name, sub.crf_section_label, sub.item_description, sub.column_name, sub.parent_dn_id, sub.entity_type, sub.description, sub.detailed_notes, sub.date_created, sub.discrepancy_note_type, sub.resolution_status, sub.discrepancy_note_owner, CASE WHEN sub.resolution_status IN ($$Closed$$, $$Not Applicable$$) THEN NULL WHEN sub.resolution_status IN ($$New$$, $$Updated$$, $$Resolution Proposed$$) THEN CURRENT_DATE - sub.date_created ELSE NULL END AS days_open, CASE WHEN sub.resolution_status IN ($$Closed$$, $$Not Applicable$$) THEN NULL WHEN sub.resolution_status IN ($$New$$, $$Updated$$, $$Resolution Proposed$$) THEN CURRENT_DATE - ( SELECT max( all_dates.date_created ) FROM (SELECT date_created FROM openclinica_fdw.discrepancy_note AS dn WHERE dn.parent_dn_id = sub.discrepancy_note_id UNION ALL SELECT date_created FROM openclinica_fdw.discrepancy_note AS dn WHERE dn.parent_dn_id = sub.parent_dn_id UNION ALL SELECT date_created FROM openclinica_fdw.discrepancy_note AS dn WHERE dn.discrepancy_note_id = sub.discrepancy_note_id ) AS all_dates ) ELSE NULL END AS days_since_update FROM dm.discrepancy_notes_all AS sub WHERE sub.parent_dn_id IS NULL GROUP BY sub.discrepancy_note_id, sub.study_name, sub.site_name, sub.subject_id, sub.event_name, sub.crf_parent_name, sub.crf_section_label, sub.item_description, sub.column_name, sub.parent_dn_id, sub.entity_type, sub.description, sub.detailed_notes, sub.date_created, sub.discrepancy_note_type, sub.resolution_status, sub.discrepancy_note_owner; CONTEXT: PL/pgSQL function dm_create_dm_discrepancy_notes_parent() line 3 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:88: ERROR: column sub.study_subject_id does not exist LINE 15: ON sgm.study_subject_id = sub.study_subject_... ^ QUERY: CREATE MATERIALIZED VIEW dm.subject_groups AS SELECT sub.study_name, sub.site_name, sub.subject_id, gct.name AS group_class_type, sgc.name AS group_class_name, sg.name AS group_name, sg.description AS group_description FROM dm.subjects AS sub INNER JOIN openclinica_fdw.subject_group_map AS sgm ON sgm.study_subject_id = sub.study_subject_id LEFT JOIN openclinica_fdw.study_group AS sg ON sg.study_group_id = sgm.study_group_id LEFT JOIN openclinica_fdw.study_group_class AS sgc ON sgc.study_group_class_id = sgm.study_group_class_id LEFT JOIN openclinica_fdw.group_class_types AS gct ON gct.group_class_type_id = sgc.group_class_type_id; CONTEXT: PL/pgSQL function dm_create_dm_subject_groups() line 3 at EXECUTE statement dm_create_dm_response_set_labels ---------------------------------- (1 row) dm_create_dm_user_account_roles --------------------------------- (1 row) psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:94: ERROR: relation "dm.clinicaldata" does not exist LINE 75: dm.clinicaldata AS cd ^ QUERY: CREATE MATERIALIZED VIEW dm.sdv_status_history AS SELECT secs.study_name, secs.subject_id, secs.event_name, secs.event_repeat, secs.event_status, secs.crf_parent_name, secs.crf_status, pale.new_value AS audit_sdv_status, pua.user_name AS audit_sdv_user, pale.audit_date AS audit_sdv_timestamp, CASE WHEN pale.audit_date IS NULL THEN NULL ELSE CASE WHEN secs.crf_sdv_status_last_updated = pale.audit_date THEN 'current' WHEN secs.crf_sdv_status_last_updated <> pale.audit_date THEN 'history' END END AS audit_sdv_current_or_history FROM ( SELECT DISTINCT ON (study_name, subject_id, event_oid, crf_version_oid) cd.study_name, cd.site_oid, cd.site_name, cd.subject_person_id, cd.subject_oid, cd.subject_id, cd.study_subject_id, cd.subject_secondary_label, cd.subject_date_of_birth, cd.subject_sex, cd.subject_enrol_date, cd.person_id, cd.subject_owned_by_user, cd.subject_last_updated_by_user, cd.event_oid, cd.event_order, cd.event_name, cd.event_repeat, cd.event_start, cd.event_end, cd.event_status, cd.event_owned_by_user, cd.event_last_updated_by_user, cd.event_crf_id, cd.crf_parent_oid, cd.crf_parent_name, cd.crf_version, cd.crf_version_oid, cd.crf_is_required, cd.crf_is_double_entry, cd.crf_is_hidden, cd.crf_null_values, cd.crf_date_created, cd.crf_last_update, cd.crf_date_completed, cd.crf_date_validate, cd.crf_date_validate_completed, cd.crf_owned_by_user, cd.crf_last_updated_by_user, cd.crf_status, cd.crf_validated_by_user, cd.crf_sdv_status, cd.crf_sdv_status_last_updated, cd.crf_sdv_by_user, cd.crf_interviewer_name, cd.crf_interview_date FROM dm.clinicaldata AS cd ) AS secs LEFT JOIN ( SELECT * FROM openclinica_fdw.audit_log_event WHERE audit_log_event_type_id = 32 ) AS pale ON pale.event_crf_id = secs.event_crf_id LEFT JOIN openclinica_fdw.user_account AS pua ON pale.user_id = pua.user_id ORDER BY secs.study_name, secs.subject_id, secs.event_name, secs.event_repeat, secs.crf_parent_name, pale.audit_date DESC CONTEXT: PL/pgSQL function dm_create_dm_sdv_status_history() line 3 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:241: ERROR: relation "slow_release_oral_formulation_of_milrinone_part_b.clinicaldata" does not exist LINE 44: ... else null end) as i_08pro_devreph_label FROM slow_relea... ^ QUERY: CREATE MATERIALIZED VIEW slow_release_oral_formulation_of_milrinone_part_b.ig_08pro_ungrouped AS SELECT study_name, site_oid, site_name, subject_id, event_oid, event_name, event_order, event_repeat, crf_parent_name, crf_version, crf_status, item_group_oid, item_group_repeat, max(case when item_oid='I_08PRO_DEVCATS' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as i_08pro_devcats , max(case when item_oid='I_08PRO_DEVCAT' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as i_08pro_devcat , max(case when item_oid='I_08PRO_DEVCAT' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as i_08pro_devcat_label , max(case when item_oid='I_08PRO_DEVREPHD' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as i_08pro_devrephd , max(case when item_oid='I_08PRO_DEVDESC' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as i_08pro_devdesc , max(case when item_oid='I_08PRO_DEVREPSD' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as i_08pro_devrepsd , max(case when item_oid='I_08PRO_DEVREPS' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as i_08pro_devreps , max(case when item_oid='I_08PRO_DEVREPS' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as i_08pro_devreps_label , max(case when item_oid='I_08PRO_DEVREPH' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as i_08pro_devreph , max(case when item_oid='I_08PRO_DEVREPH' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as i_08pro_devreph_label FROM slow_release_oral_formulation_of_milrinone_part_b.clinicaldata WHERE item_group_oid='IG_08PRO_UNGROUPED' GROUP BY study_name, site_oid, site_name, subject_id, event_oid, event_name, event_order, event_repeat, crf_parent_name, crf_version, crf_status, item_group_oid, item_group_repeat; CONTEXT: PL/pgSQL function dm_create_study_itemgroup_matviews(boolean,text,text) line 327 at EXECUTE statement psql:C:/Temp/openclinica_sqldatamart-master/dm_build_commands.sql:242: ERROR: role "dm_study_slow_release_oral_formulation_of_milrinone_part_b" does not exist CONTEXT: PL/pgSQL function dm_users_available_role_oc_user_grant_to_role() line 4 at FOR over SELECT rows dm_reassign_owner_study_matviews ---------------------------------- done (1 row) C:\Temp\openclinica_sqldatamart-master>echo 14:37:26.78 14:37:35.37 14:37:26.78 14:37:35.37 C:\Temp\openclinica_sqldatamart-master>pause Press any key to continue . . .