Having problems with message search?
Here are all the Views.
Note some of them (6) must be changed to the appropriate SQL
server syntax
I recommend just to run those you are having problems with
/* Verifying Queries First: */
ALTER VIEW ACT_PROD.AUDIT_VIEW
AS
SELECT h.*, a.QUESTION_ID, a.SUBHEADER_ID,
q.QUESTION_DESCRIPTION, a.OLDVALUE, a.NEWVALUE, a.FIELDNAME, a.USER_ID,
a.DATETIME_LAST_MODIFIED
FROM AUDIT_TABLE a, EVENT_HEADER_DETAILS_VIEW h, QUESTIONS q,
USERS u
WHERE a.QUESTION_ID=q.question_id AND a.HEADER_ID=h.header_uid
AND a.USER_ID=u.user_ID
GO
ALTER VIEW ACT_PROD.BLOB_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_blob E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.BLOB_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_BLOB E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
ALTER VIEW ACT_PROD.CDB_FORMS_VIEW
AS
SELECT C.CLUSTER_ID, c.CLUSTER_NAME, c.CLUSTER_DESCRIPTION,
c.cluster_script,
c.cluster_instruction, g.EVENT_TYPE, g.EVENT_START_LABEL,
g.EVENT_END_LABEL,
g.EVENT_FORMAT, q.*, gc.QG_SERIAL_NUMBER, gc.columns_per_row,
g.QUESTION_GROUP_ID, g.qg_script,g.qg_instruction,
g.QUESTION_GRP_DESCRIPTION, g.REPEATING_GROUP,
gq.SERIAL_NUMBER, gq.merge_with_previous_question, enter_lab_Id,
ALLOW_FUTURE_EVENT_START, ALLOW_FUTURE_EVENT_END
FROM QUESTIONS q, QUESTION_GROUPS g, CLUSTERS c,
QUESTION_GROUP_CLUSTERS gc, QUESTION_GROUP_QUESTIONS gq
WHERE c.CLUSTER_ID = gc.CLUSTER_ID AND g.QUESTION_GROUP_ID =
gc.QUESTION_GROUP_ID AND
g.QUESTION_GROUP_ID = gq.QUESTION_GROUP_ID AND q.QUESTION_ID =
gq.QUESTION_ID
GO
ALTER VIEW ACT_PROD.CLUSTERS_DVG_VIEW
AS
SELECT DISTINCT QUESTION_GROUP_CLUSTERS.CLUSTER_ID,
DISCRETE_VALUE_GROUPS.DISCRETE_VALUE_GRP_ID, DISCRETE_VALUE_GROUPS.DESCRIPTION
FROM QUESTION_GROUP_CLUSTERS, QUESTION_GROUP_QUESTIONS,
QUESTIONS, DISCRETE_VALUE_GROUPS
WHERE QUESTION_GROUP_QUESTIONS.QUESTION_ID =
QUESTIONS.QUESTION_ID AND QUESTION_GROUP_CLUSTERS.QUESTION_GROUP_ID =
QUESTION_GROUP_QUESTIONS.QUESTION_GROUP_ID AND
QUESTIONS.DISCRETE_VALUE_GROUP_ID = DISCRETE_VALUE_GROUPS.DISCRETE_VALUE_GRP_ID
GO
ALTER VIEW ACT_PROD.CLUSTERS_QUESTIONS_VIEW
AS
SELECT CLUSTER_ID, Q.QUESTION_ID, DATATYPE
FROM QUESTIONS Q, QUESTION_GROUP_QUESTIONS GQ,
QUESTION_GROUP_CLUSTERS GC where
GQ.QUESTION_GROUP_ID = GC.QUESTION_GROUP_ID
AND Q.QUESTION_ID = GQ.QUESTION_ID
GO
ALTER VIEW ACT_PROD.CLUSTERS_USERS_VIEW
AS
SELECT CLUSTERS.CLUSTER_ID, CLUSTERS.CLUSTER_NAME,
CLUSTERS.CLUSTER_DESCRIPTION, USERS.USER_ID, CLUSTERS.DATETIME_LAST_MODIFIED
FROM CLUSTERS, USERS
WHERE CLUSTERS.USER_ID = USERS.USER_ID
GO
ALTER VIEW ACT_PROD.CLUSTER_QUESTIONS_DEFAULT_VIEW
AS
SELECT QGC.CLUSTER_ID, QGQ.QUESTION_GROUP_ID, Q.QUESTION_ID,
Q.DATATYPE, QG.REPEATING_GROUP, Q.DEFAULT_VALUE
FROM QUESTION_GROUP_QUESTIONS QGQ, QUESTIONS Q,
QUESTION_GROUP_CLUSTERS QGC, QUESTION_GROUPS QG
WHERE QGQ.QUESTION_GROUP_ID=QGC.QUESTION_GROUP_ID AND
Q.DEFAULT_VALUE Is Not Null AND QGQ.QUESTION_ID=Q.QUESTION_ID
AND QG.QUESTION_GROUP_ID=QGC.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.DATE_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_date E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.DATE_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_date E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
/**** (EAV_DEFINITIONS_FOR_STUDY_VIEW) Error: 'Source_View'
is not a recognized built-in function name.
ALTER VIEW ACT_PROD.EAV_DEFINITIONS_FOR_STUDY_VIEW
AS
SELECT DISTINCT Studies_Clusters.STUDY_ID,
Questions.QUESTION_NAME Fieldname, Questions.LONG_DESCRIPTION Description,
Source_View(Questions.Datatype) Parent_View, 'True' EAV_Flag, Questions.Datatype
Data_type, Questions.QUESTION_ID, Question_Group_Questions.serial_number,
Question_Group_Questions.QUESTION_GROUP_ID, Question_Groups.EVENT_TYPE,
Question_Groups.REPEATING_GROUP,Question_Group_ClusterS.CLUSTER_ID, 'False'
Selected
FROM Questions,Question_Groups, Studies_Clusters,
Question_Group_Clusters, Question_Group_Questions
WHERE Studies_Clusters.CLUSTER_ID =
Question_Group_Clusters.CLUSTER_ID AND
Question_Group_Clusters.QUESTION_GROUP_ID =
Question_Group_Questions.QUESTION_GROUP_ID AND
Question_Groups.QUESTION_GROUP_ID =
Question_Group_Clusters.QUESTION_GROUP_ID AND
Questions.QUESTION_ID = Question_Group_Questions.QUESTION_ID
GO*/
ALTER VIEW ACT_PROD.EAV_ENUMS
AS
SELECT EAV_Int.SUBHEADER_ID, EAV_Int.QUESTION_ID, DV.Short_Name
FROM Questions Q, EAV_Int,Discrete_Values DV
WHERE Q.QUESTION_ID = EAV_Int.QUESTION_ID AND
Q.DISCRETE_VALUE_GROUP_ID = DV.Discrete_Value_Grp_ID AND
(Q.DATATYPE In ('E','O')) AND
Q.QUESTION_ID = EAV_INT.QUESTION_ID AND
Q.DISCRETE_VALUE_GROUP_ID = DV.Discrete_Value_Grp_ID AND
EAV_Int.VALUE= DV.SERIAL_NUMBER
GO
ALTER VIEW ACT_PROD.EAV_INT_DISCRETE_VALUES_VIEW
AS
SELECT EAV_INT.SUBHEADER_ID, EAV_INT.QUESTION_ID,
DISCRETE_VALUES.SHORT_NAME VALUE, EAV_INT.DATETIME
FROM DISCRETE_VALUES, QUESTIONS,EAV_INT
WHERE DISCRETE_VALUES.DISCRETE_VALUE_GRP_ID =
QUESTIONS.DISCRETE_VALUE_GROUP_ID AND
DISCRETE_VALUES.SERIAL_NUMBER = EAV_INT.VALUE
AND EAV_INT.QUESTION_ID=QUESTIONS.QUESTION_ID
GO
ALTER VIEW ACT_PROD.EAV_INT_NO_DVGS
AS
SELECT EAV_INT.SUBHEADER_ID, EAV_INT.QUESTION_ID, EAV_INT.VALUE,
eav_int.datetime
FROM EAV_INT, QUESTIONS
WHERE EAV_INT.QUESTION_ID = QUESTIONS.QUESTION_ID AND
QUESTIONS.DISCRETE_VALUE_GROUP_ID=0
GO
ALTER VIEW ACT_PROD.EH_ESH_VIEW
AS
SELECT E.*, S.SUBHEADER_ID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
S.EVENT_TYPE, S.START_OF_EVENT,S.START_OF_EVENT_MVBS, S.END_OF_EVENT,
S.END_OF_EVENT_MVBS, S.INSTANCE_LABEL, S.LAB_ID, SP.PHASE_NAME,
SP.PHASE_SERIAL_NUM
FROM EVENT_HEADER E, EVENT_SUBHEADER S, STUDY_PHASES SP
WHERE E.HEADER_UID = S.HEADER_UID and E.PHASE_ID=SP.PHASE_ID
GO
/**** (ENUM_COMBO_DATA_VIEW) Error: Incorrect syntax near
'|'.
ALTER VIEW ACT_PROD.ENUM_COMBO_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE,
E.QUESTION_ID, (E.VALUE || ':' || DV.SHORT_NAME) VALUE,
EH.STUDY_ID, EH.PATIENT_ID,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID,
DISCRETE_VALUE_ID,DISCRETE_VALUE_GRP_ID
FROM EVENT_SUBHEADER S, EAV_INT E, EVENT_HEADER EH,
STUDY_PHASES
P,DISCRETE_VALUES DV,QUESTIONS Q
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and
S.HEADER_UID = EH.HEADER_UID AND
Q.QUESTION_ID = E.QUESTION_ID AND
Q.DISCRETE_VALUE_GROUP_ID <>0 AND
Q.DISCRETE_VALUE_GROUP_ID = DV.DISCRETE_VALUE_GRP_ID
AND E.VALUE = DV.SERIAL_NUMBER
AND EH.PHASE_ID=P.PHASE_ID
GO*/
ALTER VIEW ACT_PROD.ENUM_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE,
E.QUESTION_ID, DV.SERIAL_NUMBER, DV.SHORT_NAME as VALUE,
EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID,
DISCRETE_VALUE_ID,DISCRETE_VALUE_GRP_ID
FROM EVENT_SUBHEADER S, EAV_INT E, EVENT_HEADER EH,
STUDY_PHASES
P,DISCRETE_VALUES DV,QUESTIONS Q
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and
S.HEADER_UID = EH.HEADER_UID AND
Q.QUESTION_ID = E.QUESTION_ID AND
Q.DISCRETE_VALUE_GROUP_ID <>0 AND
Q.DISCRETE_VALUE_GROUP_ID = DV.DISCRETE_VALUE_GRP_ID
AND E.VALUE = DV.SERIAL_NUMBER
AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.ENUM_STRING_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE,
E.QUESTION_ID, DV.SERIAL_NUMBER, DV.SHORT_NAME as VALUE,
EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID,
DISCRETE_VALUE_ID,DISCRETE_VALUE_GRP_ID
FROM EVENT_SUBHEADER S, EAV_INT E, EVENT_HEADER EH,
STUDY_PHASES
P,DISCRETE_VALUES DV,QUESTIONS Q
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and
S.HEADER_UID = EH.HEADER_UID AND
Q.QUESTION_ID = E.QUESTION_ID AND
Q.DISCRETE_VALUE_GROUP_ID <>0 AND
Q.DISCRETE_VALUE_GROUP_ID = DV.DISCRETE_VALUE_GRP_ID
AND E.VALUE = DV.SERIAL_NUMBER
AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.EVENT_HEADER_DETAILS_VIEW
AS
SELECT h.header_UID, h.STUDY_ID, h.PATIENT_ID, h.PHASE_ID,
h.CLUSTER_ID, c.CLUSTER_DESCRIPTION, p.FIRST_NAME,
p.MIDDLE_INIT, p.LAST_NAME, ph.PHASE_NAME,
ph.PHASE_SERIAL_NUM, s.STUDY_TITLE FROM EVENT_HEADER h, PATIENTS p, CLUSTERS
c, STUDY_PHASES ph, STUDIES s
WHERE h.STUDY_ID=s.study_id AND h.PATIENT_ID=p.patient_id AND
h.PHASE_ID=ph.phase_id AND h.CLUSTER_ID=c.cluster_ID
GO
ALTER VIEW ACT_PROD.EVENT_HEADER_VIEW
AS
SELECT EH.HEADER_UID, EH.PATIENT_ID, ST.STUDY_ID, EH.PHASE_ID,
EH.VERIFIED, EH.COMPLETED, EH.PROBLEMS,
SP.PHASE_NAME, CL.CLUSTER_ID, EH.DATE_COLLECTED, ST.STUDY_NAME,
PS.STUDY_NUMBER, PT.INITIALS, U.LNAME, U.FNAME, CL.CLUSTER_NAME,
CL.CLUSTER_DESCRIPTION, EH.DATETIME_LAST_MODIFIED,
PS.study_site, SP.PHASE_SERIAL_NUM
FROM CLUSTERS CL, STUDY_PHASES SP, USERS U, PATIENTS PT, STUDIES
ST, EVENT_HEADER EH, PATIENT_STUDY PS
WHERE ST.STUDY_ID = EH.STUDY_ID AND PS.PATIENT_ID =
EH.PATIENT_ID AND PT.PATIENT_ID = EH.PATIENT_ID AND PS.Study_ID = EH.StuDY_ID
and
U.USER_ID = EH.USER_CREATED AND SP.PHASE_ID = EH.PHASE_ID AND
CL.CLUSTER_ID = EH.CLUSTER_ID
GO
/**** (EVENT_HEADER_WEB_VIEW) Error: Incorrect syntax near
'|'.
ALTER VIEW ACT_PROD.EVENT_HEADER_WEB_VIEW
AS
SELECT DISTINCT EVENT_HEADER.HEADER_UID, EVENT_HEADER.PATIENT_ID,
EVENT_HEADER.DATE_COLLECTED, STUDIES.STUDY_ID, STUDIES.STUDY_NAME,
(PATIENTS. FIRST_NAME || ' ' || PATIENTS.MIDDLE_INIT || ' ' ||
PATIENTS.LAST_NAME) full_name, (USERS.FNAME || ' ' || USERS.LNAME)
Investigator, ROUND((SYSDATE - PATIENTS.DATE_OF_BIRTH)/365.25) Age,
PATIENTS.SEX, PATIENTS.RACE
FROM USERS, PATIENTS, STUDIES, EVENT_HEADER, PATIENT_STUDY
WHERE STUDIES.STUDY_ID = EVENT_HEADER.STUDY_ID AND
PATIENTS.PATIENT_ID = EVENT_HEADER.PATIENT_ID AND
STUDIES.STUDY_ID = PATIENT_STUDY.STUDY_ID AND
PATIENTS.PATIENT_ID = PATIENT_STUDY.PATIENT_ID AND
USERS.USER_ID = PATIENT_STUDY.USER_ID
GO*/
ALTER VIEW ACT_PROD.GROUPS_USERS_VIEW
AS
SELECT QUESTION_GROUPS.QUESTION_GROUP_ID,
QUESTION_GROUPS.QUESTION_GROUP_NAME, QUESTION_GROUPS.QUESTION_GRP_DESCRIPTION,
USERS.USER_ID, QUESTION_GROUPS.DATETIME_LAST_MODIFIED
FROM QUESTION_GROUPS, USERS
WHERE QUESTION_GROUPS.USER_ID = USERS.USER_ID
GO
ALTER VIEW ACT_PROD.INT_DATA_NODVG_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_INT E, EVENT_HEADER EH,
STUDY_PHASES P,Questions Q
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and
S.HEADER_UID = EH.HEADER_UID and
E.Question_id = q.question_id and
Q.DISCRETE_VALUE_GROUP_ID=0 AND
EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.INT_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_INT E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID
AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.INT_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_INT E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
ALTER VIEW ACT_PROD.LONGTEXT_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_memo E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.MAP_VIEW
AS
SELECT map_table.map_physical_table_uid,Map_Data.* ,
MAP_TABLE.CLUSTER_ID, Map_Table.Map_table_uID,
Map_subheader.start_date_fieldname,
Map_subheader.End_date_fieldname, Map_subheader.group_Id,
Map_subheader.lab_ID, MAP_SUBHEADER.REPEAT_LABEL_FIELDNAME,
map_subheader.rptinstance_fieldname,
Map_Physical_Table.PatientID_Fieldname,
Map_Table.PhaseID_Fieldname, Map_Table.Header_date,
Map_subheader.non_EAV_TableName
FROM Map_Table, Map_subheader, Map_Data,map_physical_table
WHERE Map_subheader.map_subheader_uID =
Map_Data.Map_subheader_uID AND
Map_Table.Map_table_uID = Map_subheader.map_table_uID and
map_physical_table.map_physical_table_uid=map_table.map_physical_table_uid
GO
ALTER VIEW ACT_PROD.MAP_VIEW_OLD
AS
SELECT map_table.map_physical_table_uid,Map_Data.*
,MAP_TABLE.CLUSTER_ID, Map_Table.Map_table_uID,
Map_subheader.start_date_fieldname, Map_subheader.End_date_fieldname,
Map_subheader.group_Id, Map_subheader.lab_ID,
MAP_SUBHEADER.REPEAT_LABEL_FIELDNAME, Map_Table.PatientID_Fieldname,
Map_Table.PhaseID_Fieldname, Map_Table.Header_date,
Map_subheader.non_EAV_TableName
FROM Map_Table, Map_subheader, Map_Data
WHERE Map_subheader.map_subheader_uID =
Map_Data.Map_subheader_uID AND
Map_Table.Map_table_uID = Map_subheader.map_table_uID
GO
ALTER VIEW ACT_PROD.MEMO_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_MEMO E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID
AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.MEMO_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_memo E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
ALTER VIEW ACT_PROD.META_DATA_VIEW
AS
SELECT distinct SC.STUDY_ID, COLOCATION_NUM, SC.CLUSTER_ID,
CL_SERIAL_NUMBER, C.CLUSTER_DESCRIPTION,
C.CLUSTER_NAME, C.COMMENTS,
C.AUTHOR, C.LITREF, C.ORIGINAL_URL, C.CLUSTER_INSTRUCTION,
C.CLUSTER_SCRIPT,C.COPYRIGHT_HOLDER,
C.INTRODUCTORY_TEXT,C.USAGE_COMPUTATION, QG.USAGE_COMPUTATION as
QG_USAGE_COMPUTATION,
QG.QG_SCRIPT, QG.QG_INSTRUCTION,
QGQ.QUESTION_GROUP_ID, Q.DISCRETE_VALUE_GROUP_ID,
QG_SERIAL_NUMBER, QUESTION_GRP_DESCRIPTION, REPEATING_GROUP,
EVENT_TYPE, EVENT_START_LABEL, EVENT_END_LABEL,
QGQ.QUESTION_ID, QGQ.SERIAL_NUMBER, QUESTION_NAME, QUESTION_DESCRIPTION,
D.TYPE_DESCRIPTION DATATYPE,
Q.LONG_DESCRIPTION,Q.QUESTION_SCRIPT,
Q.QUESTION_INSTRUCTION,Q.QUESTION_USAGENOTES,WIDTH
FROM QUESTION_GROUP_QUESTIONS QGQ,STUDIES_CLUSTERS SC, CLUSTERS
C,QUESTION_GROUP_CLUSTERS GC,
QUESTION_GROUPS QG,QUESTIONS Q,
DATATYPES D
WHERE SC.CLUSTER_ID = C.CLUSTER_ID AND
GC.QUESTION_GROUP_ID = QG.QUESTION_GROUP_ID AND
GC.QUESTION_GROUP_ID = QG.QUESTION_GROUP_ID AND
C.CLUSTER_ID = GC.CLUSTER_ID AND
QGQ.QUESTION_GROUP_ID = QG.QUESTION_GROUP_ID AND
QGQ.QUESTION_ID = Q.QUESTION_ID AND D.DATATYPE=Q.DATATYPE
GO
ALTER VIEW ACT_PROD.NON_REPEATS_VIEW
AS
SELECT EVENT_HEADER.STUDY_ID, EVENT_HEADER.PATIENT_ID,
EVENT_SUBHEADER.QUESTION_GROUP_ID, COUNT(*) EXPR1
FROM EVENT_HEADER, UNION_QUERY, EVENT_SUBHEADER
WHERE UNION_QUERY.SUBHEADER_ID = EVENT_SUBHEADER.SUBHEADER_ID
AND
EVENT_HEADER.HEADER_UID = EVENT_SUBHEADER.HEADER_UID AND
EVENT_SUBHEADER.REPEAT_INSTANCE=0
GROUP BY EVENT_HEADER.STUDY_ID, EVENT_HEADER.PATIENT_ID,
EVENT_SUBHEADER.QUESTION_GROUP_ID
HAVING (((COUNT(*))=1))
GO
ALTER VIEW ACT_PROD.NON_REPEAT_DATA_VIEW
AS
SELECT EVENT_HEADER.STUDY_ID, EVENT_HEADER.PATIENT_ID,
UNION_QUERY.QUESTION_ID, COUNT(*) EXPR1
FROM EVENT_HEADER, UNION_QUERY, EVENT_SUBHEADER
WHERE UNION_QUERY.SUBHEADER_ID = EVENT_SUBHEADER.SUBHEADER_ID
AND
EVENT_HEADER.HEADER_UID = EVENT_SUBHEADER.HEADER_UID
GROUP BY EVENT_HEADER.STUDY_ID, EVENT_HEADER.PATIENT_ID,
UNION_QUERY.QUESTION_ID
HAVING (((COUNT(*))=1))
GO
ALTER VIEW ACT_PROD.NON_REPEAT_QUESTIONS_VIEW
AS
SELECT DISTINCT Q.QUESTION_ID, Q.LONG_DESCRIPTION, Q.DATATYPE,
N.QUESTION_GROUP_ID, GC.CLUSTER_ID, GC.QG_SERIAL_NUMBER,
QUESTION_GROUP_QUESTIONS.SERIAL_NUMBER, N.STUDY_ID
FROM QUESTIONS Q, QUESTION_GROUPS, STUDIES_CLUSTERS,
QUESTION_GROUP_CLUSTERS GC,
NON_REPEATS_VIEW N, QUESTION_GROUP_QUESTIONS
WHERE STUDIES_CLUSTERS.CLUSTER_ID = GC.CLUSTER_ID AND
STUDIES_CLUSTERS.STUDY_ID = N.STUDY_ID AND
QUESTION_GROUPS.QUESTION_GROUP_ID = GC.QUESTION_GROUP_ID AND
QUESTION_GROUPS.QUESTION_GROUP_ID = N.QUESTION_GROUP_ID AND
QUESTION_GROUPS.QUESTION_GROUP_ID =
QUESTION_GROUP_QUESTIONS.QUESTION_GROUP_ID AND
Q.QUESTION_ID = QUESTION_GROUP_QUESTIONS.QUESTION_ID
GO
ALTER VIEW ACT_PROD.NUMERIC_DATA_VIEW
AS
SELECT SUBHEADER_ID, EAV_INT.QUESTION_ID, VALUE FROM EAV_INT,
QUESTIONS
WHERE QUESTIONS.QUESTION_ID=EAV_INT.QUESTION_ID
UNION
SELECT SUBHEADER_ID, QUESTION_ID, VALUE FROM EAV_REAL
GO
/**** (PATIENTS_DEIDENTIFIED_VIEW) Error: 'TO_CHAR' is not a
recognized built-in function name.
ALTER VIEW ACT_PROD.PATIENTS_DEIDENTIFIED_VIEW
AS
SELECT HISPANIC_ETHNICITY ,
patients.PATIENT_ID ,
TO_CHAR(DATE_OF_BIRTH,'YYYY') DATE_OF_BIRTH ,
ESTDOB ,
SEX ,
ZIP ,
RACE ,
PATIENTS.DATETIME_CREATED ,
OTHER_RACE ,
RACE_DETAILS ,
HISPANIC_ORIGIN ,
INITIALS,
STUDY_ID,
STUDY_SITE
FROM PATIENTS,PATIENT_STUDY
WHERE PATIENTS.PATIENT_ID=patient_study.patient_ID
GO*/
ALTER VIEW ACT_PROD.PATIENTS_STUDY_VIEW
AS
SELECT DISTINCT PS.*, S.TIME_UNITS, P.DATE_OF_BIRTH,
P.FIRST_NAME, P.MIDDLE_INIT, P.LAST_NAME, U.LNAME, U.FNAME, P.INITIALS,
P.SOC_SEC_NO, P.RACE, P.SEX,
P.HISPANIC_ORIGIN,P.RACE_DETAILS,P.CGN_ID, P.mrun,P.Address, P.City, P.State,
P.Zip,
P.WKPHONE,P.home_phone,P.fax,P.mrun2,S.LABIMPBYPHASE,P.FAMILY_ID,
ST.SITE as SITE_NAME,p.TESTPATIENT
FROM STUDIES S, PATIENTS P, PATIENT_STUDY PS, USERS U, SITES ST
where U.USER_ID = PS.USER_ID
and P.PATIENT_ID = PS.PATIENT_ID AND ST.SITE_ID = PS.STUDY_SITE
AND
S.STUDY_ID = PS.STUDY_ID
GO
ALTER VIEW ACT_PROD.PATIENT_APPTS_TEMPLATES_VIEW
AS
SELECT PS.*, PSP.PHASE_ID, PSP.PHASE_START_DATE, PSP.PHASE_ACTUAL_DATE,
PSP.END_VISIT_DATE, PSP.VISIT_LOCATION, PSP.VISIT_PERSON, PSP.COMMENTS AS
PSPCOMMENTS, PSP.STATUS
FROM PATIENT_STUDY_PHASES PSP, PATIENTS_STUDY_VIEW PS
where PSP.STUDY_ID = PS.STUDY_ID AND
PSP.PATIENT_ID = PS.PATIENT_ID
GO
ALTER VIEW ACT_PROD.PATIENT_SCHEDULED_EVENTS_VIEW
AS
SELECT STUDY_PHASES.PHASE_NAME, STUDY_PHASES.PHASE_SERIAL_NUM,
CLUSTERS.CLUSTER_ID, CLUSTERS.CLUSTER_NAME,
PATIENT_STUDY_PHASES_VIEW.PHASE_START_DATE,
PATIENT_STUDY_PHASES_VIEW.PHASE_ACTUAL_DATE, STUDY_PHASES.STUDY_ID,
STUDY_PHASES.PHASE_ID, PATIENT_STUDY_PHASES_VIEW.PATIENT_ID
FROM STUDY_PHASES_CLUSTERS, PATIENT_STUDY_PHASES_VIEW, CLUSTERS,
STUDY_PHASES
WHERE STUDY_PHASES_CLUSTERS.STUDY_ID =
PATIENT_STUDY_PHASES_VIEW.STUDY_ID AND
STUDY_PHASES_CLUSTERS.PHASE_ID =
PATIENT_STUDY_PHASES_VIEW.PHASE_ID AND STUDY_PHASES_CLUSTERS.CLUSTER_ID =
CLUSTERS.CLUSTER_ID AND
STUDY_PHASES_CLUSTERS.PHASE_ID = STUDY_PHASES.PHASE_ID
GO
ALTER VIEW ACT_PROD.PATIENT_STUDY_CRITERIA_VIEW
AS
SELECT DISTINCT PATIENT_STUDY_CRITERIA.patient_id,PATIENT_STUDY_CRITERIA.study_criteria_id,study_criteria.study_id,study_criteria.criterion_type,study_criteria.criterion_number,patient_study_criteria.response,
study_criteria.criterion_text,STUDY_SITE
FROM STUDY_CRITERIA, PATIENT_STUDY_CRITERIA,PATIENT_STUDY
WHERE patient_study_criteria.study_criteria_id =
study_criteria.study_criteria_id AND
study_criteria.study_id=PATIENT_STUDY.STUDY_ID AND
PATIENT_STUDY.PATIENT_ID=PATIENT_STUDY_CRITERIA.PATIENT_ID
GO
ALTER VIEW ACT_PROD.PATIENT_STUDY_PHASES_DATA_VIEW
AS
SELECT PS.*, psp.PHASE_ID, psp.PHASE_START_DATE,
psp.PHASE_ACTUAL_DATE, psp.END_VISIT_DATE, psp.VISIT_LOCATION,
psp.VISIT_PERSON, psp.COMMENTS AS Visit_Comments, psp.STATUS
FROM PATIENTS_STUDY_VIEW PS, PATIENT_STUDY_PHASES psp
WHERE PS.PATIENT_ID=psp.PATIENT_ID AND PS.STUDY_ID=psp.STUDY_ID
GO
ALTER VIEW ACT_PROD.PATIENT_STUDY_PHASES_GRID_VIEW
AS
SELECT STUDIES.STUDY_ID, PATIENT_ID,
PHASE_SERIAL_NUM, OFFSET_FROM_START, UPPER_OFFSET_BOUND,
LOW_OFFSET_BOUND, PHASE_ID, PHASE_NAME, PHASE_START_DATE,
VISIT_LOCATION, VISIT_PERSON, END_VISIT_DATE, PHASE_ACTUAL_DATE,
TIME_UNITS, STATUS, COMMENTS, PHASE_DVG_ID,
DATE_CORRESPONDENCE_SENT,
DATE_RETURN_CORRESPONDENCE, SHOW_ON_CALENDAR, CHECKLIST
FROM PATIENT_STUDY_PHASES_VIEW, STUDIES where
PATIENT_STUDY_PHASES_VIEW.STUDY_ID = STUDIES.STUDY_ID
GO
ALTER VIEW ACT_PROD.PATIENT_STUDY_PHASES_VIEW
AS
SELECT DISTINCT PATIENT_STUDY_PHASES.*, PHASE_NAME,
PHASE_SERIAL_NUM, OFFSET_FROM_START, LOW_OFFSET_BOUND,UPPER_OFFSET_BOUND,
PHASE_DVG_ID,SHOW_ON_CALENDAR,CHECKLIST, STUDY_SITE
FROM STUDY_PHASES, PATIENT_STUDY_PHASES,PATIENT_STUDY
WHERE STUDY_PHASES.PHASE_ID = PATIENT_STUDY_PHASES.PHASE_ID AND
PATIENT_STUDY.STUDY_ID=PATIENT_STUDY_PHASES.STUDY_ID AND
PATIENT_STUDY.PATIENT_ID=PATIENT_STUDY_PHASES.PATIENT_ID
GO
ALTER VIEW ACT_PROD.PEDIGREE_LEGEND_VIEW
AS
SELECT q.STUDY_ID, q.QUESTION_ID, q.SECTOR,
c.DEFINED_TRAIT_VALUE, c.SECTOR_COLOR
FROM PEDIGREE_LEGEND_CHOICES c ,
PEDIGREE_LEGEND_QUESTIONS q WHERE c.PEDIGREE_LEGEND_UID =
q.PEDIGREE_LEGEND_UID
GO
ALTER VIEW ACT_PROD.PROBLEM_MESSAGES_VIEW
AS
SELECT EH.STUDY_ID, EH.PATIENT_ID, EH.CLUSTER_ID, EH.PHASE_ID,
PM.QUESTION_ID,
PM.NOTE_TEXT, PM.NOTE_STATUS, PM.NOTE_CREATER, PM.NOTE_DATE,
PMR.NOTE_RESPONSE,
PMR.NOTE_RESPONSE_DATE, PMR.NOTE_RESPONDER, EH.DATE_COLLECTED,
PM.SUBHEADER_ID,
PM.EVENT_FIELD
FROM (EVENT_HEADER EH INNER JOIN PROBLEM_MESSAGES PM ON
EH.HEADER_UID = PM.HEADER_ID)
LEFT JOIN PROBLEM_MESSAGE_RESPONSES PMR ON PM.PROBLEM_ID =
PMR.PROBLEM_ID
GO
ALTER VIEW ACT_PROD.PT_PHASE_CLUSTER_DATA_VIEW
AS
SELECT
EVENT_HEADER.STUDY_ID,EVENT_HEADER.PATIENT_ID,STUDY_PHASES.PHASE_NAME,event_header.phase_id,
PATIENT_STUDY_PHASES.PHASE_START_DATE,PATIENT_STUDY_PHASES.PHASE_ACTUAL_DATE,event_header.cluster_id,CLUSTERS.CLUSTER_DESCRIPTION,EVENT_HEADER.COMPLETED,
EVENT_HEADER.PROBLEMS,EVENT_HEADER.DATE_COLLECTED,PATIENT_STUDY_PHASES.STATUS,PATIENT_STUDY_PHASES.COMMENTS,
patient_study.study_site, SITES.SITE
FROM EVENT_HEADER,CLUSTERS, PATIENT_STUDY_PHASES, STUDY_PHASES,
patient_study,SITES
WHERE EVENT_HEADER.CLUSTER_ID = CLUSTERS.CLUSTER_ID AND
SITES.SITE_ID=PATIENT_STUDY.STUDY_SITE AND
EVENT_HEADER.PATIENT_ID = PATIENT_STUDY_PHASES.PATIENT_ID AND
EVENT_HEADER.STUDY_ID = PATIENT_STUDY_PHASES.STUDY_ID AND
EVENT_HEADER.PHASE_ID = PATIENT_STUDY_PHASES.PHASE_ID AND
PATIENT_STUDY_PHASES.PHASE_ID = STUDY_PHASES.PHASE_ID and
patient_study.study_id = event_header.study_id and
patient_study.patient_id = event_header.patient_id
GO
ALTER VIEW ACT_PROD.QG_CLUSTERS_STUDIES_VIEW
AS
SELECT DISTINCT QUESTION_GROUP_CLUSTERS.CLUSTER_ID,
QUESTION_GROUP_CLUSTERS.QUESTION_GROUP_ID,
STUDIES_QUESTION_GROUPS_VIEW.STUDY_ID
FROM QUESTION_GROUP_CLUSTERS, STUDIES_QUESTION_GROUPS_VIEW
WHERE QUESTION_GROUP_CLUSTERS.QUESTION_GROUP_ID =
STUDIES_QUESTION_GROUPS_VIEW.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.QG_CLUSTERS_VIEW
AS
SELECT DISTINCT QUESTION_GROUP_CLUSTERS.*,CLUSTERS.CLUSTER_NAME,
CLUSTERS.CLUSTER_DESCRIPTION, QUESTION_GROUPS.QUESTION_GROUP_NAME,
QUESTION_GROUPS.QUESTION_GRP_DESCRIPTION, QUESTION_GROUPS.COMMENTS,
QUESTION_GROUPS.REPEATING_GROUP
FROM QUESTION_GROUPS, CLUSTERS, QUESTION_GROUP_CLUSTERS
WHERE CLUSTERS.CLUSTER_ID = QUESTION_GROUP_CLUSTERS.CLUSTER_ID
AND
CLUSTERS.CLUSTER_ID = QUESTION_GROUP_CLUSTERS.CLUSTER_ID AND
QUESTION_GROUPS.QUESTION_GROUP_ID =
QUESTION_GROUP_CLUSTERS.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.QUALIFIED_QUESTIONS_VIEW
AS
SELECT Qualifier_Dependents.Cluster_Question_ID,
Qualifying_Questions.Question_ID,
Qualifying_Questions.Cluster_ID, QUESTIONS_1.DATATYPE,
Qualifier_Dependents.Value1,
Qualifier_Dependents.value2, Qualifier_Dependents.Operator,
Qualifier_Dependents.Disabled_Question_ID,
QUESTIONS_1.QUESTION_DESCRIPTION Qualified,
QUESTIONS.QUESTION_DESCRIPTION Disabled,
Qualifier_Dependents.Default_Value
FROM Qualifying_Questions, QUESTIONS, Qualifier_Dependents,
questions questions_1
where QUESTIONS_1.QUESTION_ID = Qualifying_Questions.Question_ID
and QUESTIONS.QUESTION_ID =
Qualifier_Dependents.Disabled_Question_ID
and Qualifying_Questions.Cluster_question_ID =
Qualifier_Dependents.Cluster_Question_ID
GO
ALTER VIEW ACT_PROD.QUESTIONS_CLUSTERS_VIEW
AS
SELECT gc.CLUSTER_ID, gq.QUESTION_ID, q.QUESTION_NAME,
q.DATATYPE, gc.QUESTION_GROUP_ID, q.QUESTION_DESCRIPTION,
q.LONG_DESCRIPTION, gc.QG_Serial_Number, gq.Serial_Number
FROM QUESTION_GROUP_CLUSTERS gc, QUESTION_GROUP_QUESTIONS gq,
QUESTIONS q
WHERE gc.QUESTION_GROUP_ID = gq.QUESTION_GROUP_ID AND
gq.QUESTION_ID = q.QUESTION_ID
GO
ALTER VIEW ACT_PROD.QUESTIONS_DVS_VIEW
AS
SELECT DV.SERIAL_NUMBER, DV.SHORT_NAME, DV.DESCRIPTION,
DV.DISCRETE_VALUE_GRP_ID,QUESTION_ID, DISCRETE_VALUE_ID
FROM QUESTIONS Q, DISCRETE_VALUES DV
where Q.DISCRETE_VALUE_GROUP_ID = DV.DISCRETE_VALUE_GRP_ID
GO
ALTER VIEW ACT_PROD.QUESTIONS_QGROUPS_VIEW
AS
SELECT DISTINCT QUESTION_GROUP_QUESTIONS.QUESTION_ID,
QUESTIONS.QUESTION_NAME, QUESTIONS.DATATYPE,
QUESTION_GROUP_QUESTIONS.QUESTION_GROUP_ID,
QUESTIONS.QUESTION_DESCRIPTION,QUESTIONS.LONG_DESCRIPTION,
Question_Group_Questions.Serial_Number
FROM QUESTION_GROUP_QUESTIONS, QUESTIONS
WHERE QUESTION_GROUP_QUESTIONS.QUESTION_ID =
QUESTIONS.QUESTION_ID
GO
ALTER VIEW ACT_PROD.RANLIST_VIEW
AS
SELECT RANLIST.RANLIST_ID, PATIENT_STUDY.STUDY_ID,
RANLIST.STRATUM_NUMBER, RANLIST.DATE_ASSIGNED, RANLIST.PATIENT_ID, RANPROTOCOLS.RANPROTOCOL_DESCRIPTION,
RANPROTOCOLS_TREATMENTS.TREATMENT_DESCRIPTION, PATIENT_STUDY.STUDY_SITE,
PATIENTS.FAMILY_ID
FROM (((RANLIST INNER JOIN RANPROTOCOLS ON
RANLIST.RANPROTOCOL_ID = RANPROTOCOLS.RANPROTOCOL_ID) INNER JOIN
RANPROTOCOLS_TREATMENTS ON (RANPROTOCOLS_TREATMENTS.TREATMENT_NUMBER =
RANLIST.TREATMENT_NUMBER) AND (RANPROTOCOLS.RANPROTOCOL_ID =
RANPROTOCOLS_TREATMENTS.RANPROTOCOL_ID)) INNER JOIN PATIENT_STUDY ON
(RANLIST.PATIENT_ID = PATIENT_STUDY.PATIENT_ID) AND (RANPROTOCOLS.STUDY_ID =
PATIENT_STUDY.STUDY_ID)) INNER JOIN PATIENTS ON RANLIST.PATIENT_ID =
PATIENTS.PATIENT_ID
GO
ALTER VIEW ACT_PROD.REAL_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,E.MVBS,
S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID, P.PHASE_NAME,
P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_real E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.REAL_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_real E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
ALTER VIEW ACT_PROD.REQUIRED_QUESTIONS_BY_CLUSTER
AS
SELECT STUDY_ID, CLUSTER_ID, Count(QUESTION_ID) Ct
FROM REQUIRED_QUESTIONS
GROUP BY STUDY_ID, CLUSTER_ID
GO
ALTER VIEW ACT_PROD.REQUIRED_QUESTIONS_QG_VIEW
AS
SELECT STUDY_ID, R.CLUSTER_ID, QC.QUESTION_GROUP_ID,
R.QUESTION_ID
FROM REQUIRED_QUESTIONS R, QUESTION_GROUP_CLUSTERS QC,
QUESTION_GROUP_QUESTIONS QQ
WHERE R.CLUSTER_ID = QC.CLUSTER_ID
and QQ.QUESTION_ID = R.QUESTION_ID
AND QC.QUESTION_GROUP_ID = QQ.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.REQUIRED_QUESTIONS_SUBHEADERS
AS
SELECT PATIENT_STUDY.STUDY_ID, EVENT_SUBHEADER.SUBHEADER_ID,
REQUIRED_QUESTIONS.QUESTION_ID, PATIENT_STUDY.PATIENT_ID
FROM REQUIRED_QUESTIONS, PATIENT_STUDY, EVENT_HEADER,
EVENT_SUBHEADER
WHERE EVENT_HEADER.HEADER_UID = EVENT_SUBHEADER.HEADER_UID AND
PATIENT_STUDY.STUDY_ID = EVENT_HEADER.STUDY_ID AND
REQUIRED_QUESTIONS.CLUSTER_ID = EVENT_HEADER.CLUSTER_ID
GROUP BY PATIENT_STUDY.STUDY_ID, EVENT_SUBHEADER.SUBHEADER_ID,
REQUIRED_QUESTIONS.QUESTION_ID, PATIENT_STUDY.PATIENT_ID
GO
ALTER VIEW ACT_PROD.REQUIRED_QUESTIONS_VIEW
AS
SELECT REQUIRED_QUESTIONS.STUDY_ID,
REQUIRED_QUESTIONS.CLUSTER_ID, REQUIRED_QUESTIONS.QUESTION_ID,
QUESTIONS_CLUSTERS_VIEW.QUESTION_DESCRIPTION
FROM REQUIRED_QUESTIONS, QUESTIONS_CLUSTERS_VIEW WHERE
REQUIRED_QUESTIONS.QUESTION_ID = QUESTIONS_CLUSTERS_VIEW.QUESTION_ID AND
REQUIRED_QUESTIONS.CLUSTER_ID = QUESTIONS_CLUSTERS_VIEW.CLUSTER_ID
GO
ALTER VIEW ACT_PROD.STRING_DATA_VIEW
AS
SELECT s.subheader_ID, S.HEADER_UID, S.QUESTION_GROUP_ID,
S.REPEAT_INSTANCE, E.QUESTION_ID, E.VALUE,EH.STUDY_ID, EH.PATIENT_ID,
E.MVBS,S.START_OF_EVENT, S.END_OF_EVENT, EH.PHASE_ID,
P.PHASE_NAME, P.PHASE_SERIAL_NUM,EH.CLUSTER_ID
FROM EVENT_SUBHEADER S, EAV_string E, EVENT_HEADER EH,
STUDY_PHASES P
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID and S.HEADER_UID =
EH.HEADER_UID
AND EH.PHASE_ID=P.PHASE_ID
GO
ALTER VIEW ACT_PROD.STRING_SUB_VIEW
AS
SELECT S.HEADER_UID, S.QUESTION_GROUP_ID, S.REPEAT_INSTANCE,
E.QUESTION_ID, E.VALUE
FROM EVENT_SUBHEADER S, EAV_string E
WHERE S.SUBHEADER_ID=E.SUBHEADER_ID
GO
ALTER VIEW ACT_PROD.STUDIES_DVG_VIEW
AS
SELECT DISTINCT s.STUDY_ID, study_Name,
Q.DISCRETE_VALUE_GROUP_ID
FROM STUDIES_CLUSTERS SC, QUESTION_GROUP_CLUSTERS GC, QUESTION_GROUP_QUESTIONS
GQ, QUESTIONS Q, Studies S
WHERE SC.CLUSTER_ID = GC.CLUSTER_ID AND S.Study_ID = SC.Study_ID
and
GQ.QUESTION_ID = Q.QUESTION_ID AND
GC.QUESTION_GROUP_ID = GQ.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.STUDIES_QUESTION_GROUPS_VIEW
AS
SELECT DISTINCT s.STUDY_ID,S.study_Name, gc.QUESTION_GROUP_ID
FROM STUDIES_CLUSTERS sc, QUESTION_GROUP_CLUSTERS gc, Studies S
WHERE sc.CLUSTER_ID= gc.CLUSTER_ID and sc.study_ID = S.study_ID
GO
ALTER VIEW ACT_PROD.STUDIES_QUESTION_VIEW
AS
SELECT DISTINCT S.STUDY_ID, s.Study_Name, QUESTION_ID
FROM QUESTION_GROUP_QUESTIONS GQ, STUDIES_CLUSTERS SC,
QUESTION_GROUP_CLUSTERS GC, Studies S
WHERE SC.CLUSTER_ID = GC.CLUSTER_ID AND S.Study_ID =
SC.Study_ID and
GQ.QUESTION_GROUP_ID = gc.QUESTION_GROUP_ID
GO
ALTER VIEW ACT_PROD.STUDY_CLUSTERS_QUESTIONS_VIEW
AS
SELECT Sc.STUDY_ID, GC.CLUSTER_ID, GQ.QUESTION_ID
FROM QUESTION_GROUP_QUESTIONS GQ, QUESTION_GROUP_CLUSTERS GC,
STUDIES_CLUSTERS sc
where GQ.QUESTION_GROUP_ID = GC.QUESTION_GROUP_ID and GC.CLUSTER_ID
=sc.CLUSTER_ID
GO
ALTER VIEW ACT_PROD.STUDY_CLUSTERS_VIEW
AS
SELECT DISTINCT STUDIES_CLUSTERS.STUDY_ID,
CLUSTERS.CLUSTER_DESCRIPTION, STUDIES_CLUSTERS.CLUSTER_ID,
STUDIES_CLUSTERS.CL_SERIAL_NUMBER, STUDIES.STUDY_NAME, CLUSTERS.CLUSTER_NAME
FROM STUDIES, CLUSTERS, STUDIES_CLUSTERS
WHERE CLUSTERS.CLUSTER_ID = STUDIES_CLUSTERS.CLUSTER_ID
AND STUDIES.STUDY_ID = STUDIES_CLUSTERS.STUDY_ID
GO
ALTER VIEW ACT_PROD.STUDY_SITE_INST_VIEW
AS
SELECT STUDIES_SITES.STUDY_ID, SITES.INSTITUTIONUID,
STUDIES_SITES.SITE_ID, INSTITUTIONS.INSTITUTION, Sites.Site
FROM STUDIES_SITES, SITES, INSTITUTIONS
WHERE STUDIES_SITES.SITE_ID=SITES.SITE_ID AND
SITES.INSTITUTIONUID = INSTITUTIONS.INSTITUTIONUID
GO
/**** (STUDY_USERS_VIEW) Error: Incorrect syntax near '|'.
ALTER VIEW ACT_PROD.STUDY_USERS_VIEW
AS
SELECT DISTINCT USERS.USER_NAME, STUDIES.STUDY_NAME,
USERS_PRIVILEGES.STUDY_ID, USERS_PRIVILEGES.USER_ID, (STUDIES.STUDY_ID || ':'
|| USERS_PRIVILEGES.USER_ID || ':' || USERS_PRIVILEGES.ADD_DATA || ':' ||
USERS_PRIVILEGES.DELETE_DATA || ':' || USERS_PRIVILEGES.UPDATE_DATA || ':' ||
USERS_PRIVILEGES.VIEW_DATA || ':' || USERS_PRIVILEGES.SITE_ID || ':' ||
USERS_PRIVILEGES.UPLOAD_PRIV || ':' || USERS_PRIVILEGES.ADHOC_PRIV)
STUDYIDUSERID
FROM USERS, USERS_PRIVILEGES, STUDIES
WHERE USERS.USER_ID = USERS_PRIVILEGES.USER_ID AND
USERS_PRIVILEGES.STUDY_ID = STUDIES.STUDY_ID
GO*/
/**** (UNION_QUERY) Error: 'TO_CHAR' is not a recognized
built-in function name.
'TO_CHAR' is not a recognized built-in function name.
'TO_CHAR' is not a recognized built-in function name.
ALTER VIEW ACT_PROD.UNION_QUERY
AS
SELECT SUBHEADER_ID,QUESTION_ID, VALUE FROM EAV_STRING
UNION
SELECT SUBHEADER_ID, EAV_INT.QUESTION_ID, TO_CHAR(VALUE) FROM
EAV_INT, QUESTIONS
WHERE QUESTIONS.QUESTION_ID=EAV_INT.QUESTION_ID
AND DATATYPE NOT IN ('E','O')
UNION
SELECT SUBHEADER_ID, EAV_INT.QUESTION_ID, dv.SHORT_NAME FROM
EAV_INT, QUESTIONS Q,DISCRETE_VALUES DV
WHERE DATATYPE IN ('E','O')
AND Q.QUESTION_ID=EAV_INT.QUESTION_ID
AND Q.DISCRETE_VALUE_GROUP_ID = DV.DISCRETE_VALUE_GRP_ID
AND VALUE=DV.SERIAL_NUMBER
UNION SELECT SUBHEADER_ID, QUESTION_ID, TO_CHAR( ROUND (
VALUE,3)) FROM EAV_REAL
UNION SELECT SUBHEADER_ID, QUESTION_ID, TO_CHAR(VALUE) FROM
EAV_DATE
GO*/
ALTER VIEW ACT_PROD.VOCAB_VALUE_CATEGORIES_VIEW
AS
SELECT v.VOCABULARY_UID, c.CATEGORY_UID, c.CATEGORY_NAME,
v.VERSION_NUMBER, v.CODEINSOURCE_ID, v.UMLS_CUID, v.LOOKUP, v.ABBREV,
v.PARENT_TEXT, v.PARENT_CODEINSOURCE_ID
FROM VOCAB_CATEGORIE_VALUES cv,VOCABULARY_CATEGORIES c,
VOCABULARY_VALUES v
where
cv.CATEGORY_UID = c.CATEGORY_UID and cv.VOCABULARY_VALUE_UID
= v.VOCABULARY_VALUE_UID
GO
________________________
Luis Marenco
Yale Center for Medical Informatics
From:
trialdb@yahoogroups.com [mailto:trialdb@yahoogroups.com] On Behalf Of Raj
K Muthu
Sent: Monday, August 27, 2007 11:24 AM
To: trialdb@yahoogroups.com
Subject: [trialdb] Re: Installation issue
Luis/Prakash,
Thank you for the quick response. Following your suggestions, I
got passed the .Net version issue and able to get to the login screen.
Upon login (picked up an existing user from USERS table), I'm getting
the following error.
It looks to me there is a mismatch between the QUERY in
TrialDB.aspx.vb and the database view definition.In STUDY_USERS_VIEW,
the user privileges are compressed in one column "STUDYIDUSERID", but
the query is written with the assumption they're individual columns in
the view.
Any thoughts?
Thanks.
Regards,
Raj K
PS: I apologize for mistakenly posting the same question multiple time.
Error in Routine:GetRows: Syntax or semantic Error in SQL query:
select V.study_ID,
V.user_ID,
V.add_data,
V.delete_data,
V.update_data,
V.view_data,
V.site_id,
V.upload_priv,
V.adhoc_priv,
S.Study_Title
from STUDY_USERS_VIEW V,
STudies S
where S.Study_ID=V.Study_ID
AND USER_ID = 2541
ORDER BY STUDY_TITLE
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Source File: C:\Inetpub\wwwroot\trialDB\trialDB.aspx.vb Line: 52
--- In trialdb@yahoogroups.com,
Luis Marenco <luis.marenco@...> wrote:
>
> The latest version we are using is the Visual Studio 2005. It is bit
by bit the
> same as our production-Oracle one.
>
>
>
> To use this one you need to do the following:
>
>
>
> - SQL server 2005 (any version from Express through
Enterprise). We
> rely upon SQL server schemas and synonyms to share the same code as our
> production one on both the Web and Access.
>
> - The access client is exactly the same bit-by-bite to our
production
> one and will work without any modifications; but you will need Windows
> integrated security for every user.
>
> - You need IIS, with .NET 2.0 enabled
>
>
>
> To configure your system to work properly:
>
> - You need to change all ADMINVAR to your project settings
>
> - Verify IIS folder sec
Offline 
Send Email