SELECT AM.INDICATOR , AM.ANALYSIS_OPTION0 AS A0 , AM.ANALYSIS_OPTION1 AS A1 , AM.ANALYSIS_OPTION2 AS A2 , AM.SERIES_ID AS SERIES_ID , OPT.DIM_SET_ID , AM.DATASET_ID FROM BSC_KPI_ANALYSIS_MEASURES_VL AM , ( /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 0 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR , AO.OPTION_ID AS A0 , -1 AS A1 , -1 AS A2 , AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_VL AO , BSC_KPI_ANALYSIS_GROUPS AG WHERE AO.INDICATOR = AG.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID AND AG.ANALYSIS_GROUP_ID = 0 AND AG.CHANGE_DIM_SET =1 UNION /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 1 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR , DECODE(AG.DEPENDENCY_FLAG , 1 , AO.PARENT_OPTION_ID , -1) AS A0 , AO.OPTION_ID AS A1 , -1 AS A2 , AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_VL AO , BSC_KPI_ANALYSIS_GROUPS AG WHERE AO.INDICATOR = AG.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID AND AG.ANALYSIS_GROUP_ID = 1 AND AG.CHANGE_DIM_SET =1 UNION /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 2 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR , DECODE(AG2.DEPENDENCY_FLAG , 1 , AO.GRANDPARENT_OPTION_ID , -1) AS A0 , DECODE(AG1.DEPENDENCY_FLAG , 1 , AO.PARENT_OPTION_ID , -1) AS A1 , AO.OPTION_ID AS A2 , AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_VL AO , BSC_KPI_ANALYSIS_GROUPS AG1 , BSC_KPI_ANALYSIS_GROUPS AG2 WHERE AO.INDICATOR = AG1.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG1.ANALYSIS_GROUP_ID AND AG1.ANALYSIS_GROUP_ID = 2 AND AG1.CHANGE_DIM_SET= 1 AND AG2.ANALYSIS_GROUP_ID = 1 AND AG1.INDICATOR = AG2.INDICATOR ) OPT WHERE AM.INDICATOR = OPT.INDICATOR AND (OPT.A0 =AM.ANALYSIS_OPTION0 OR OPT.A0 = -1) AND (OPT.A1 = AM.ANALYSIS_OPTION1 OR OPT.A1 = -1) AND (OPT.A2 = AM.ANALYSIS_OPTION2 OR OPT.A2 = -1)