Toad World® Forums

Query formation for different number of columns from different table


#1

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