How obtain null values.......

[raf]

Hi,
I've table COD_TEST:
LS_ID..........FL_ID...........RM_TYPE.......................DV_ID.................................. .....DP_ID
01000026........PTE.......ARCHIVI/DEPOSITI..................DOMESTIC WIRELINE........DW.RT
01000026........PTE.......LOCALI APPARATI PER TLC...........DOMESTIC WIRELINE........DW.RT
01000026........PTE.......LOCALI APPARATI PER TLC...............TIM..................TIM
01000026........PTE.......LOCALI TECNOLOGICI DI EDIFICIO....DOMESTIC WIRELINE........DW.RT
01000026........PTE.......PERCORSI ORIZZONTALI.............................................
01000026........PTE.......SCALE..................................................................... ...............
01000026........PTE.......SERVIZI IGIENICI.................................................
01000026........PTE.......UFFICI TRADIZIONALI/OPEN SPACE....DOMESTIC WIRELINE........DW.RT
01000026........PTE.......VIABILITA`................................................................ .........................
01000026........S01.......ALTRI SPAZI DI SERVIZIO...........DOMESTIC WIRELINE........DW.RT
01000026........S01.......ARCHIVI/DEPOSITI..................DOMESTIC WIRELINE........DW.RT
01000026........S01.......CAVEDI.................................................................... ....................................
01000026........S01.......LOCALI APPARATI PER TLC...........DOMESTIC WIRELINE........DW.RT
01000026........S01.......LOCALI APPARATI PER TLC...........ULL........ULL
01000026........S01.......LOCALI TECNOLOGICI DI EDIFICIO....DOMESTIC WIRELINE........DW.RT


TABLE DP_TEST:
DV_ID..............................DP_ID.......................SNOP
DOMESTIC WIRELINE...................DW.RT...........T03
DOMESTIC WIRELINE...................DW.RU...........T03
DOMESTIC WIRELINE...................DW.STP..........T03
DOMESTIC WIRELINE...................DW.SWI..........EMS
DOMESTIC WIRELINE...................DW.SWN..........EMS
TIM.............................................TIM....................TIM
ULL.............................................ULL....................T03
WIND............................................WIND...................T03



TABLE TI_USE_TEST:
RM_TYPE......................COD_RITMI...........DESCR_RITMI..................TIPO_SPAZIO
ARCHIVI/DEPOSITI..................01...........Uff., Centri formaz.- CED............O
CAVEDI..............................................................................V
LOCALI APPARATI PER TLC...........06............Centrali Non Presid...................
SCALE.............................01.........Uff., Centri formaz.- CED...............V
SERVIZI IGIENICI..................01..........Uff., Centri formaz.- CED...............
LOCALI APPARATI PER TLC...........06..........Centrali Non Presid.....................
LOCALI APPARATI PER TLC...........05..........Centrali Presidiate.....................
LOCALI TECNOLOGICI DI EDIFICIO......05..........Centrali Presidiate...................
AULE DI FORMAZIONE..................01.........Uff., Centri formaz.- CED..............
CENTRO DI ELABORAZIONE DATI (CED)...01..........Uff., Centri formaz.- CED.............

I'd like to obtain all data, also with dv_id is null

I tried this query:

select a.ls_id, a.FL_ID,a.RM_TYPE,b.DV_ID,c.COD_RITMI,c.DESCR_RITMI,c.TIPO_SPAZIO
from cod_test a, dp_test b, ti_use_test c
where a.dv_id=b.DV_ID(+)
and a.RM_TYPE=c.RM_TYPE(+)
AND B.SNOP='T03'

But I get just data with dv_id is not null

How can I write my query to obtain all values (also rm_type with dv_id is null)??

I tried also: AND B.SNOP(+)='T03' but I get also dv_id='TIM', but I'd like to obtain all values with snop='T03' (also with dv_id is null)

Thanks!!

[DaPi]

How about:
AND (B.SNOP='T03' or B.SNOP is NULL)

[raf]

Originally posted by DaPi
How about:
AND (B.SNOP='T03' or B.SNOP is NULL)

I tried this:

select a.ls_id, a.FL_ID, a.RM_TYPE,
b.DV_ID,
c.COD_RITMI, c.DESCR_RITMI, c.TIPO_SPAZIO
from cod_test a, dp_test b, ti_use_test c
where a.ls_id in
(select ls_id
from cod_test
where dv_id in
(select dv_id
from dp_test
where snop = 'T03'))
and a.dv_id = b.dv_id (+)
and a.rm_type = c.rm_type (+)
and a.dv_id is null


In this case I get just dv_id null (with snop='T03')
but I'd like to obtain also dv_id ='DOMESTIC WIRELINE'

Thanks in advance!

[zzz]

Raf,

I think you would get the result that you are looking for if you follow what DaPi has suggested. He has asked you to try this:

select a.ls_id, a.FL_ID,a.RM_TYPE,b.DV_ID,c.COD_RITMI,c.DESCR_RITMI,c.TIPO_SPAZIO
from cod_test a, dp_test b, ti_use_test c
where a.dv_id=b.DV_ID(+)
and a.RM_TYPE=c.RM_TYPE(+)
AND (B.SNOP='T03' or B.SNOP is NULL)