Scenario:
Area Cell mapping data is available in one master table oasis_mstr_area_cell. It contains the info about cells belonging to respective area.
Ex: AREA1 can have mapping with different cells from different table.
AREA 1 can have 10 cells from Motorola, 30 cells from Alcatel and 50 from Ericsson.
Likewise for all areas, and it will change dynamically.
We have a list of common and uncommon indicators for each vendor. We need to generate a report cells belong to area. So we have to fetch Indicators list from different vendor tables.
Example:
I tried two approach, first I not getting data if anyone table is empty
SELECT
FLOOR(AL_GPRS.date_val),
AREA.AREA_CODE,
SUM(P105F),
SUM(P105G),
SUM(P10),
SUM(MC01),
SUM(MC02),
SUM(MC02A),
SUM(GPRS_ACC_PER_AGCH)
FROM
val_al_ggprsncelhr AL_GPRS,
val_al_ggsmxncelhr AL_GSM ,
val_mo_ggprsncelhr MO_GPRS,
oasis_mstr_area_cell AREA
WHERE
AL_GPRS.date_val = 2010060101
AND AL_GSM.date_val= 2010060101
AND MO_GPRS.date_val = 2010060101
AND AL_GPRS.cell_uid(+) = area.cell_uid
AND AL_GSM.cell_uid(+) = area.cell_uid
AND MO_GPRS.cell_uid(+) = area.cell_uid
GROUP BY
FLOOR (AL_GPRS.DATE_VAL) ,
AREA.AREA_CODE
And second one, is not equal number of columns problem
If added 0 or null, it takes column list from a first query only.
SELECT * FROM(
SELECT Val.DATE_VAL, AREA.Area_code, SUM(MC02A) aaa, SUM(MC02B) bbb
FROM oasis_mstr_area_cell AREA,
val_al_ggsmxncelhr Val
WHERE AREA.Cell_Uid = VAL.Cell_Uid and date_val between 2010060101 and 2010060101
GROUP BY Val.DATE_VAL, AREA.Area_code
UNION ALL
SELECT Val.DATE_VAL, AREA.Area_code, SUM(P105F) aaa, SUM(P105D) bbb
FROM oasis_mstr_area_cell AREA,
val_al_ggprsncelhr Val
WHERE AREA.Cell_Uid = VAL.Cell_Uid and date_val between 2010060101 and 2010060101
GROUP BY Val.DATE_VAL, AREA.Area_code
UNION ALL
SELECT Val.DATE_VAL, AREA.Area_code, SUM(GPRS_ACC_PER_AGCH) aaa, SUM(AIR_UL_BLKS_Q1_CS1) bbb
FROM oasis_mstr_area_cell AREA,
val_MO_ggprsncelhr Val
WHERE AREA.Cell_Uid = VAL.Cell_Uid and date_val between 2010060101 and 2010060101
GROUP BY Val.DATE_VAL, AREA.Area_code);
Please advice me.
Thanks