reliability-platform-project/SQL/view_sql

CREATE OR REPLACE VIEW V_SYS_DELIVERY_MD
(DOCUMENT_ID,DOCUMENT_NAME,DOCUMENT_TYPE,LIFE_STATE,RELEASE_FLAG,INSERT_TIME,INSERT_USER_ID)
AS
SELECT DOCUMENT_ID, DOCUMENT_NAME, '002', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_BS_DOCUMENT UNION
SELECT PROJECT_INFO_ID, PROJECT_NAME, '004', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_WB_PROJECT_INFO UNION
SELECT PROJECT_ID, PROJECT_NAME, '005', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_WDA_PROJECT UNION
SELECT ASSESSMENT_ID, ASSESSMENT_NAME, '014', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_RA_ASSESSMENT UNION
SELECT PA_ANALYSIS_ID, PA_ANALYSIS_NAME, '015', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_PA_ANALYSIS UNION
SELECT TS_ID, TS_NAME, '019', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_TS_INFO WHERE TARGET_TYPE='31' UNION
SELECT TS_ID, TS_NAME, '020', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_TS_INFO WHERE TARGET_TYPE='32' UNION
SELECT PLAN_ID, PLAN_NAME, '022', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_RGT_PLAN UNION
SELECT SCHEDULE_ID, SCHEDULE_NAME, '023', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_RGT_SCHEDULE UNION
SELECT TRACK_ID, TRACK_NAME, '024', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_RGT_TRACK UNION
SELECT ANALYSIS_ID, ANALYSIS_NAME, '031', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_LD_ANALYSIS UNION
SELECT MULTI_CHART_ID, MULTI_CHART_NAME, '032', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_LD_MULTI_CHART UNION
SELECT DA_ID, DA_NAME, '034', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_DA_INFO UNION
SELECT FMEA_ID, FMEA_NAME, '035', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_DFMEA_INFO UNION
SELECT T.FMEA_ID, N.FMEA_NAME, '045', T.LIFE_STATE, if(T.LIFE_STATE='20', 1, 0) RELEASE_FLAG, T.INSERT_TIME, T.INSERT_USER_ID FROM T_RDFM_INFO T INNER JOIN T_RDFM_INFO_NLS N ON T.FMEA_ID=N.FMEA_ID AND N.LKEY=1 UNION
SELECT T.FMEA_ID, N.FMEA_NAME, '046', T.LIFE_STATE, if(T.LIFE_STATE='20', 1, 0) RELEASE_FLAG, T.INSERT_TIME, T.INSERT_USER_ID FROM T_RPFM_INFO T INNER JOIN T_RPFM_INFO_NLS N ON T.FMEA_ID=N.FMEA_ID AND N.LKEY=1 UNION
SELECT PD_ID, PD_NAME, '037', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_PD_INFO UNION
SELECT FMEA_ID, FMEA_NAME, '038', LIFE_STATE, if(LIFE_STATE='20', 1, 0) RELEASE_FLAG, INSERT_TIME, INSERT_USER_ID FROM T_PFMEA_INFO;