2015-04-20 10:27:59 HKT 日志: 语句: /* give usage so any dm_admin can access and refresh foreign objects */ GRANT USAGE ON FOREIGN SERVER openclinica_fdw_server TO dm_admin; 2015-04-20 10:27:59 HKT 日志: 语句: /* impersonate dm_admin so that the following commands create objects owned by dm_admin */ SET ROLE dm_admin; 2015-04-20 10:27:59 HKT 日志: 语句: /* add foreign tables for catalog tables for looking up objects and their definitions */ SELECT dm_create_ft_catalog(); 2015-04-20 10:27:59 HKT 日志: 语句: /* add foreign tables for each table or view in the specified openclinica schema */ SELECT dm_create_ft_openclinica( 'public'); 2015-04-20 10:28:10 HKT 日志: 语句: /* add matviews for openclinica tables to cache them locally for reporting */ SELECT dm_create_ft_openclinica_matviews(); 2015-04-20 10:28:22 HKT 日志: 语句: /* add indexes to the openclinica matviews that existed in the foreign schema */ SELECT dm_create_ft_openclinica_matview_indexes( 'public'); 2015-04-20 10:28:36 HKT 日志: 语句: /* add dm matview with choice lists split to rows, re-used many times so not a cte */ SELECT dm_create_dm_response_sets(); 2015-04-20 10:28:36 HKT 日志: 语句: /* change query planner parameter, improves execution time of dm.metadata query */ SET seq_page_cost = 0.25; 2015-04-20 10:28:36 HKT 日志: 语句: /* add dm matview with study metadata for all items in all events and studies */ SELECT dm_create_dm_metadata(); 2015-04-20 10:28:37 HKT 日志: 语句: /* change query planner parameter back to default */ SET seq_page_cost = 1.0; 2015-04-20 10:28:37 HKT 日志: 语句: /* add dm matview with study metadata for all itemgroups in all events and studies */ SELECT dm_create_dm_metadata_event_crf_ig(); 2015-04-20 10:28:37 HKT 日志: 语句: /* add dm matview with study metadata for all items in all studies */ SELECT dm_create_dm_metadata_crf_ig_item(); 2015-04-20 10:28:37 HKT 日志: 语句: /* add dm matview with study list and their schema names */ SELECT dm_create_dm_metadata_study(); 2015-04-20 10:28:37 HKT 日志: 语句: /* add index on non-blank item_data values which helps the dm_clinicaldata query */ CREATE INDEX i_item_id_value_notblank ON item_data USING btree (item_id) WHERE ("value" <> $$$$); 2015-04-20 10:28:37 HKT 日志: 语句: /* add dm matview with item data and related site or study metadata */ SELECT dm_create_dm_clinicaldata(); 2015-04-20 10:28:40 HKT 日志: 语句: /* change query planner parameter back to default */ SET join_collapse_limit = 8; 2015-04-20 10:28:40 HKT 日志: 语句: /* add index on study_name and item_group_oid as these are common filters */ CREATE INDEX i_dm_clinicaldata_study_name_item_group_oid ON dm.clinicaldata USING BTREE (study_name, item_group_oid); 2015-04-20 10:28:40 HKT 日志: 语句: /* add dm matview with subjects in all studies */ SELECT dm_create_dm_subjects(); 2015-04-20 10:28:40 HKT 日志: 语句: /* add dm matview with event and crf statuses for each subject in each study */ SELECT dm_create_dm_subject_event_crf_status(); 2015-04-20 10:28:41 HKT 日志: 语句: /* add dm matview with possible events and crfs for each subject in each study */ SELECT dm_create_dm_subject_event_crf_expected(); 2015-04-20 10:28:41 HKT 日志: 语句: /* add dm matview with expected and current event and crf statuses for each subject in each study */ SELECT dm_create_dm_subject_event_crf_join(); 2015-04-20 10:28:41 HKT 错误: 字段 s.study_subject_id 不存在 第 248 个字符处 2015-04-20 10:28:41 HKT 查询: 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 2015-04-20 10:28:41 HKT 上下文: 在EXECUTE 语句的第3行的PL/pgSQL函数dm_create_dm_subject_event_crf_join() 2015-04-20 10:28:41 HKT 语句: /* add dm matview with expected and current event and crf statuses for each subject in each study */ SELECT dm_create_dm_subject_event_crf_join(); 2015-04-20 10:28:41 HKT 日志: 语句: /* add dm matview with discrepancy notes in each study */ SELECT dm_create_dm_discrepancy_notes_all(); 2015-04-20 10:28:41 HKT 错误: 字段 cd.item_data_id 不存在 第 3011 个字符处 2015-04-20 10:28:41 HKT 查询: 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; 2015-04-20 10:28:41 HKT 上下文: 在EXECUTE 语句的第3行的PL/pgSQL函数dm_create_dm_discrepancy_notes_all() 2015-04-20 10:28:41 HKT 语句: /* add dm matview with discrepancy notes in each study */ SELECT dm_create_dm_discrepancy_notes_all(); 2015-04-20 10:28:41 HKT 日志: 语句: /* add dm matview with parent discrepancy notes in each study */ SELECT dm_create_dm_discrepancy_notes_parent(); 2015-04-20 10:28:41 HKT 错误: 关系 "dm.discrepancy_notes_all" 不存在 第 2285 个字符处 2015-04-20 10:28:41 HKT 查询: 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; 2015-04-20 10:28:41 HKT 上下文: 在EXECUTE 语句的第3行的PL/pgSQL函数dm_create_dm_discrepancy_notes_parent() 2015-04-20 10:28:41 HKT 语句: /* add dm matview with parent discrepancy notes in each study */ SELECT dm_create_dm_discrepancy_notes_parent(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add dm matview with subject groups for each subject in each study */ SELECT dm_create_dm_subject_groups(); 2015-04-20 10:28:42 HKT 错误: 字段 sub.study_subject_id 不存在 第 498 个字符处 2015-04-20 10:28:42 HKT 查询: 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; 2015-04-20 10:28:42 HKT 上下文: 在EXECUTE 语句的第3行的PL/pgSQL函数dm_create_dm_subject_groups() 2015-04-20 10:28:42 HKT 语句: /* add dm matview with subject groups for each subject in each study */ SELECT dm_create_dm_subject_groups(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add dm matview with reponse sets for each item in each crf in each study */ SELECT dm_create_dm_response_set_labels(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add dm matview with study roles for each user account in the instance */ SELECT dm_create_dm_user_account_roles(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add dm matview with sdv status history for each subject event crf */ SELECT dm_create_dm_sdv_status_history(); 2015-04-20 10:28:42 HKT 错误: 字段 cd.study_subject_id 不存在 第 1195 个字符处 2015-04-20 10:28:42 HKT 查询: 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 2015-04-20 10:28:42 HKT 上下文: 在EXECUTE 语句的第3行的PL/pgSQL函数dm_create_dm_sdv_status_history() 2015-04-20 10:28:42 HKT 语句: /* add dm matview with sdv status history for each subject event crf */ SELECT dm_create_dm_sdv_status_history(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add a view for running the set of study schema object creation functions */ CREATE VIEW dm.build_study_functions AS SELECT dm_create_study_schemas( study_name), dm_create_study_common_matviews( study_name), dm_create_study_itemgroup_matviews( FALSE, study_name), dm_create_study_itemgroup_matviews( TRUE, study_name) AS dm_create_study_itemgroup_matviews_av, dm_create_study_role( study_name), dm_grant_study_schema_access_to_study_role( study_name) FROM ( SELECT DISTINCT study_name FROM dm.metadata AS dmd WHERE dmd.study_status != $$removed$$ AND NOT EXISTS ( SELECT n.nspname AS schemaname FROM pg_class AS c LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relkind = $$m$$ AND dm_clean_name_string( dmd.study_name) = n.nspname ORDER BY c.oid ) ) AS study_names; 2015-04-20 10:28:42 HKT 日志: 语句: /* add a view for running user management functions */ CREATE VIEW dm.user_management_functions AS SELECT dm_users_new_oc_user_new_login_role(), dm_users_removed_oc_user_alter_role_nologin(), dm_users_restored_oc_user_alter_role_login(), dm_users_available_role_oc_user_grant_to_role(), dm_users_removed_role_oc_user_revoke_from_role(); 2015-04-20 10:28:42 HKT 日志: 语句: /* add a view for refreshing all openclinica foreign table matviews*/ CREATE VIEW dm.refresh_matviews_openclinica_fdw AS SELECT dm_refresh_matview( mv.schemaname, mv.matviewname) FROM ( SELECT n.nspname AS schemaname, c.relname AS matviewname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $$m$$ AND n.nspname = $$openclinica_fdw$$ ORDER BY c.oid ) AS mv; 2015-04-20 10:28:42 HKT 日志: 语句: /* add a view for refreshing all datamart schema matviews */ CREATE VIEW dm.refresh_matviews_dm AS SELECT dm_refresh_matview( mv.schemaname, mv.matviewname) FROM ( SELECT n.nspname AS schemaname, c.relname AS matviewname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $$m$$ AND n.nspname = $$dm$$ ORDER BY c.oid ) AS mv; 2015-04-20 10:28:42 HKT 日志: 语句: /* add a view for refreshing all study schema matviews. refresh if study is available, or if the study is locked or frozen then refresh until end of day after last update */ CREATE VIEW dm.refresh_matviews_study AS SELECT dm_refresh_matview( mv.schemaname, mv.matviewname) FROM ( SELECT n.nspname AS schemaname, c.relname AS matviewname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace INNER JOIN ( SELECT ddmd.study_name FROM ( SELECT DISTINCT ON (dmd.study_name) dmd.study_name, dmd.study_status, dmd.study_date_updated FROM dm.metadata AS dmd ) AS ddmd WHERE ddmd.study_status = $$available$$ OR ( ddmd.study_status IN ($$locked$$, $$frozen$$) AND ddmd.study_date_updated >= (date_trunc( $$day$$, now() ) - INTERVAL '1 day') ) ) AS study_names ON dm_clean_name_string( study_names.study_name) = n.nspname WHERE c.relkind = $$m$$ AND c.relname !=$$timestamp_schema$$ ORDER BY c.oid ) AS mv; 2015-04-20 10:28:42 HKT 日志: 语句: /* change back to postgres user for createrole permission to run views */ SET ROLE postgres; 2015-04-20 10:28:42 HKT 日志: 语句: TABLE dm.build_study_functions; 2015-04-20 10:28:46 HKT 错误: 字段 "crf_version" 不存在 第 208 个字符处 2015-04-20 10:28:46 HKT 查询: CREATE MATERIALIZED VIEW xxx.ig__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__AECONCO3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeconco3_ , max(case when item_oid='I__AECONCO3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeconco3__label , max(case when item_oid='I__AERESDR2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeresdr2_ , max(case when item_oid='I__AERESDR2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeresdr2__label , max(case when item_oid='I__AESTDTT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aestdtt1_ , max(case when item_oid='I__AESVRES2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aesvres2_ , max(case when item_oid='I__AETERM1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aeterm1_ , max(case when item_oid='I__AENUM3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aenum3_ , max(case when item_oid='I__AESV3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesv3_ , max(case when item_oid='I__AESV3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesv3__label , max(case when item_oid='I__AEACN3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeacn3_ , max(case when item_oid='I__AEACN3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeacn3__label , max(case when item_oid='I__AESTDTT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aestdtt2_ , max(case when item_oid='I__AESEQ2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeseq2_ , max(case when item_oid='I__AESEQ2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeseq2__label , max(case when item_oid='I__AEENDTT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeendtt1_ , max(case when item_oid='I__AESEV3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesev3_ , max(case when item_oid='I__AESEV3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesev3__label , max(case when item_oid='I__AESTDT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aestdt3_ , max(case when item_oid='I__AEOUT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeout2_ , max(case when item_oid='I__AEOUT2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeout2__label , max(case when item_oid='I__AESV1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesv1_ , max(case when item_oid='I__AESV1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesv1__label , max(case when item_oid='I__AEACN1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeacn1_ , max(case when item_oid='I__AEACN1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeacn1__label , max(case when item_oid='I__AESVRES1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aesvres1_ , max(case when item_oid='I__AERESDR1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeresdr1_ , max(case when item_oid='I__AERESDR1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeresdr1__label , max(case when item_oid='I__AEENDT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aeendt2_ , max(case when item_oid='I__AENUM1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aenum1_ , max(case when item_oid='I__AESTDT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aestdt2_ , max(case when item_oid='I__AETERM2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aeterm2_ , max(case when item_oid='I__AEREL1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aerel1_ , max(case when item_oid='I__AEREL1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aerel1__label , max(case when item_oid='I__AESTDT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aestdt1_ , max(case when item_oid='I__AENUM2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aenum2_ , max(case when item_oid='I__AESVRES3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aesvres3_ , max(case when item_oid='I__AESEV2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesev2_ , max(case when item_oid='I__AESEV2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesev2__label , max(case when item_oid='I__AESEQ3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeseq3_ , max(case when item_oid='I__AESEQ3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeseq3__label , max(case when item_oid='I__AEREL2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aerel2_ , max(case when item_oid='I__AEREL2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aerel2__label , max(case when item_oid='I__AECONCO1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeconco1_ , max(case when item_oid='I__AECONCO1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeconco1__label , max(case when item_oid='I__AESEV1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesev1_ , max(case when item_oid='I__AESEV1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesev1__label , max(case when item_oid='I__AEENDT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aeendt3_ , max(case when item_oid='I__AEPAT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aepat3_ , max(case when item_oid='I__AEPAT3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aepat3__label , max(case when item_oid='I__AEENDT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as DATE ) end) else null end) as aeendt1_ , max(case when item_oid='I__AEENDTT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeendtt2_ , max(case when item_oid='I__AERESDR3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeresdr3_ , max(case when item_oid='I__AERESDR3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeresdr3__label , max(case when item_oid='I__AEACN2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeacn2_ , max(case when item_oid='I__AEACN2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeacn2__label , max(case when item_oid='I__AEOUT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeout3_ , max(case when item_oid='I__AEOUT3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeout3__label , max(case when item_oid='I__AECONCO2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeconco2_ , max(case when item_oid='I__AECONCO2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeconco2__label , max(case when item_oid='I__AESTDTT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aestdtt3_ , max(case when item_oid='I__AEOUT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeout1_ , max(case when item_oid='I__AEOUT1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeout1__label , max(case when item_oid='I__AESV2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aesv2_ , max(case when item_oid='I__AESV2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aesv2__label , max(case when item_oid='I__AEYN' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeyn_ , max(case when item_oid='I__AEYN' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeyn__label , max(case when item_oid='I__AEREL3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aerel3_ , max(case when item_oid='I__AEREL3' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aerel3__label , max(case when item_oid='I__AETERM3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as text ) end) else null end) as aeterm3_ , max(case when item_oid='I__AESEQ1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeseq1_ , max(case when item_oid='I__AESEQ1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aeseq1__label , max(case when item_oid='I__AEPAT1' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aepat1_ , max(case when item_oid='I__AEPAT1' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aepat1__label , max(case when item_oid='I__AEENDTT3' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aeendtt3_ , max(case when item_oid='I__AEPAT2' then (case when item_value = '' then null when item_value IN ('') then null else cast(item_value as numeric ) end) else null end) as aepat2_ , max(case when item_oid='I__AEPAT2' then (case when item_value = '' then null when item_value IN ('') then null else option_text end) else null end) as aepat2__label FROM xxx.clinicaldata WHERE item_group_oid='IG__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; 2015-04-20 10:28:46 HKT 上下文: 在EXECUTE 语句的第327行的PL/pgSQL函数dm_create_study_itemgroup_matviews(boolean,text,text) 2015-04-20 10:28:46 HKT 语句: TABLE dm.build_study_functions; 2015-04-20 10:28:47 HKT 日志: 语句: TABLE dm.user_management_functions; 2015-04-20 10:28:47 HKT 错误: 角色 "dm_study_xxx" 不存在 2015-04-20 10:28:47 HKT 上下文: 在在SELECT记录上的FOR语句的第4行的PL/pgSQL函数dm_users_available_role_oc_user_grant_to_role() 2015-04-20 10:28:47 HKT 语句: TABLE dm.user_management_functions; 2015-04-20 10:28:47 HKT 日志: 语句: /* reassign ownership of the study matviews created above to dm_admin so it can do refresh */ SELECT dm_reassign_owner_study_matviews();