View ESEC.ESECDBA.RPT_LABELS_RPT_V | Generated by SchemaSpy |
View SQL:
SELECT EN_ALL.RPT_NAME AS RPT_NAME, COALESCE(LANG_COUNTRY.LABEL_1, LANG_ALL.LABEL_1, EN_ALL.LABEL_1 ) AS LABEL_1 , -- Handles only 'NULL' values COALESCE(LANG_COUNTRY.LABEL_2, LANG_ALL.LABEL_2, EN_ALL.LABEL_2 ) AS LABEL_2 , COALESCE(LANG_COUNTRY.LABEL_3, LANG_ALL.LABEL_3, EN_ALL.LABEL_3 ) AS LABEL_3 , COALESCE(LANG_COUNTRY.LABEL_4, LANG_ALL.LABEL_4, EN_ALL.LABEL_4 ) AS LABEL_4, COALESCE(LANG_COUNTRY.LABEL_5, LANG_ALL.LABEL_5, EN_ALL.LABEL_5 ) AS LABEL_5, COALESCE(LANG_COUNTRY.LABEL_6, LANG_ALL.LABEL_6, EN_ALL.LABEL_6 ) AS LABEL_6, COALESCE(LANG_COUNTRY.LABEL_7, LANG_ALL.LABEL_7, EN_ALL.LABEL_7 ) AS LABEL_7, COALESCE(LANG_COUNTRY.LABEL_8, LANG_ALL.LABEL_8, EN_ALL.LABEL_8 ) AS LABEL_8, COALESCE(LANG_COUNTRY.LABEL_9, LANG_ALL.LABEL_9, EN_ALL.LABEL_9 ) AS LABEL_9, COALESCE(LANG_COUNTRY.LABEL_10,LANG_ALL.LABEL_10,EN_ALL.LABEL_10 )AS LABEL_10, COALESCE(LANG_COUNTRY.LABEL_11,LANG_ALL.LABEL_11,EN_ALL.LABEL_11 )AS LABEL_11, COALESCE(LANG_COUNTRY.LABEL_12,LANG_ALL.LABEL_12,EN_ALL.LABEL_12 )AS LABEL_12, COALESCE(LANG_COUNTRY.LABEL_13,LANG_ALL.LABEL_13,EN_ALL.LABEL_13 )AS LABEL_13, COALESCE(LANG_COUNTRY.LABEL_14,LANG_ALL.LABEL_14,EN_ALL.LABEL_14 )AS LABEL_14, COALESCE(LANG_COUNTRY.LABEL_15,LANG_ALL.LABEL_15,EN_ALL.LABEL_15 )AS LABEL_15, COALESCE(LANG_COUNTRY.LABEL_16,LANG_ALL.LABEL_16,EN_ALL.LABEL_16 )AS LABEL_16, COALESCE(LANG_COUNTRY.LABEL_17,LANG_ALL.LABEL_17,EN_ALL.LABEL_17 )AS LABEL_17, COALESCE(LANG_COUNTRY.LABEL_18,LANG_ALL.LABEL_18,EN_ALL.LABEL_18 )AS LABEL_18, COALESCE(LANG_COUNTRY.LABEL_19,LANG_ALL.LABEL_19,EN_ALL.LABEL_19 )AS LABEL_19, COALESCE(LANG_COUNTRY.LABEL_20,LANG_ALL.LABEL_20,EN_ALL.LABEL_20 )AS LABEL_20, COALESCE(LANG_COUNTRY.LABEL_21,LANG_ALL.LABEL_21,EN_ALL.LABEL_21 )AS LABEL_21, COALESCE(LANG_COUNTRY.LABEL_22,LANG_ALL.LABEL_22,EN_ALL.LABEL_22 )AS LABEL_22, COALESCE(LANG_COUNTRY.LABEL_23,LANG_ALL.LABEL_23,EN_ALL.LABEL_23 )AS LABEL_23, COALESCE(LANG_COUNTRY.LABEL_24,LANG_ALL.LABEL_24,EN_ALL.LABEL_24 )AS LABEL_24, COALESCE(LANG_COUNTRY.LABEL_25,LANG_ALL.LABEL_25,EN_ALL.LABEL_25 )AS LABEL_25, COALESCE(LANG_COUNTRY.LABEL_26,LANG_ALL.LABEL_26,EN_ALL.LABEL_26 )AS LABEL_26, COALESCE(LANG_COUNTRY.LABEL_27,LANG_ALL.LABEL_27,EN_ALL.LABEL_27 )AS LABEL_27, COALESCE(LANG_COUNTRY.LABEL_28,LANG_ALL.LABEL_28,EN_ALL.LABEL_28 )AS LABEL_28, COALESCE(LANG_COUNTRY.LABEL_29,LANG_ALL.LABEL_29,EN_ALL.LABEL_29 )AS LABEL_29, COALESCE(LANG_COUNTRY.LABEL_30,LANG_ALL.LABEL_30,EN_ALL.LABEL_30 )AS LABEL_30, COALESCE(LANG_COUNTRY.LABEL_31,LANG_ALL.LABEL_31,EN_ALL.LABEL_31 )AS LABEL_31, COALESCE(LANG_COUNTRY.LABEL_32,LANG_ALL.LABEL_32,EN_ALL.LABEL_32 )AS LABEL_32, COALESCE(LANG_COUNTRY.LABEL_33,LANG_ALL.LABEL_33,EN_ALL.LABEL_33 )AS LABEL_33, COALESCE(LANG_COUNTRY.LABEL_34,LANG_ALL.LABEL_34,EN_ALL.LABEL_34 )AS LABEL_34, COALESCE(LANG_COUNTRY.LABEL_35,LANG_ALL.LABEL_35,EN_ALL.LABEL_35 )AS LABEL_35 FROM ( SELECT RPT_TRANSLATION.* FROM RPT_TRANSLATION WHERE UPPER(RPT_TRANSLATION.RPT_LANG) = 'ALL' AND UPPER(RPT_TRANSLATION.RPT_COUNTRY) = 'ALL' ) EN_ALL -- To get the labels for the reports for LANGUAGE = 'ALL' , COUNTRY = 'ALL' LEFT OUTER JOIN ( SELECT RPT_TRANSLATION.* FROM RPT_TRANSLATION WHERE UPPER(RPT_TRANSLATION.RPT_LANG) = ( SELECT UPPER(CONFIG_VALUE) FROM NLS_CONFIG WHERE UPPER(CONFIG_NAME) = 'LANGUAGE' AND UPPER(USER_NAME) = 'ESECRPT' AND UPPER(COMPONENT) = 'REPORT') AND UPPER(RPT_TRANSLATION.RPT_COUNTRY) = ( SELECT UPPER(CONFIG_VALUE) FROM NLS_CONFIG WHERE UPPER(CONFIG_NAME) = 'COUNTRY' AND UPPER(USER_NAME) = 'ESECRPT' AND UPPER(COMPONENT) = 'REPORT') ) LANG_COUNTRY ON LANG_COUNTRY.RPT_NAME = EN_ALL.RPT_NAME -- To get the labels for the reports for specified COUNTRY and LANGUAGE Eg: LANGUAGE = 'ZH' , COUNTRY = 'CN' LEFT OUTER JOIN (SELECT RPT_TRANSLATION.* FROM RPT_TRANSLATION WHERE UPPER(RPT_TRANSLATION.RPT_LANG) = ( SELECT UPPER(CONFIG_VALUE) FROM NLS_CONFIG WHERE UPPER(CONFIG_NAME) = 'LANGUAGE' AND UPPER(USER_NAME) = 'ESECRPT' AND UPPER(COMPONENT) = 'REPORT') AND UPPER(RPT_TRANSLATION.RPT_COUNTRY) = 'ALL' ) LANG_ALL ON LANG_ALL.RPT_NAME = EN_ALL.RPT_NAME |