Toad World® Forums

Query formation for different number of columns from different table


#1

Message from: angaps.ap.mca_625

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


Historical Messages

Author: angaps.ap.mca_625
Date: Wed Nov 10 21:54:48 PST 2010
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
__