Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: how obtain null values......

    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.............................................TI M....................TIM
    ULL.............................................UL L....................T03
    WIND............................................WI ND...................T03



    TABLE TI_USE_TEST:
    RM_TYPE......................COD_RITMI...........D ESCR_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_RITM I,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!!

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    It's been a LONG while since I did this, so I'm sure to be corrected, but I'd thought I'd answer anyway, to see how much I've remembered (or rather, how much I haven't forgotten!):

    The main problem with your statement is "where a.dv_id=b.DV_ID" - this will return all records where these 2 fields are the same. If all you're after is those that are null then try "where a.dvd_id is NULL".

    (Sits back & waits to be told of all the things he said wrong...)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about
    Code:
    SELECT a.ls_id, a.rm_type, b.dv_id, c.cod_ritmi
      FROM cod_test a, dp_test b, ti_use_test c
     WHERE a.rm_type = c.rm_type (+)
       AND (a.dv_id = b.dv_id
            OR a.dv_id IS NULL
           )
       AND b.snop = 't03';

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    actually, you almost had it. try

    select a.ls_id, a.FL_ID,a.RM_TYPE,b.DV_ID,c.COD_RITMI,c.DESCR_RITM I,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'

    By not making the test for T03 a join, you forced the query to say that it could only be satisfied by a join where a.dv_id - b.dv_id were equal and b.snop equaled T03.

    What the join does is make it look for the matches where b.snop equals T03 or any other records that don't have a matching record in dp_test.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by beilstwh
    actually, you almost had it. try

    select a.ls_id, a.FL_ID,a.RM_TYPE,b.DV_ID,c.COD_RITMI,c.DESCR_RITM I,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'

    By not making the test for T03 a join, you forced the query to say that it could only be satisfied by a join where a.dv_id - b.dv_id were equal and b.snop equaled T03.

    What the join does is make it look for the matches where b.snop equals T03 or any other records that don't have a matching record in dp_test.

    ok, my problem is this:

    I'd like to obtain just the ls_id that has (snop='T03' and dv_id is null).

    with your query I get all ls_id with snop='T03' and also all ls_id with dv_id is null.

    How can I get only ls_id with (snop='T03' and dv_id is null)??

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just curious - what is the result of a query in 3rd post here? You never said that ...

  7. #7
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by Littlefoot
    Just curious - what is the result of a query in 3rd post here? You never said that ...
    for example:

    LS_ID....FL_ID....RM_TYPE.......DV_ID............. DP_ID
    001.......P01.........AAAAA...............DOM..... .DOM
    001.......P01.........BBBBB....................... .......
    001.......P02.........CCCCC...............MOR..... .MOR
    001.......P02.........DDDDD....................... ....
    009.......P01.........AAAAA...............MOR..... .MOR
    009.......P01.........CCCCC....................... ....
    009.......P01.........BBBBB...............DOM..... .DOM
    009.......P02.........CCCCC....................... ....
    008.......P01.........AAAAA....................... ....
    008.......P02.........BBBBB...............MOR..... .MOR
    006.......P04.........CCCCC....................... ....
    006.......P04.........AAAAA....................... ....
    006.......P02.........BBBBB...............MOR..... .MOR


    TABLE DP_TEST:
    DV_ID....DP_ID......SNOP
    DOM........DOM........T03
    MOR........MOR........TIM

    TABLE TI_USE_TEST:
    RM_TYPE........COD_RITMI.......DESCR_RITMI........ .TIPO_SPAZIO
    AAAAA............01......................AA....... .................O
    BBBBB............02......................BB....... .................V
    CCCCC............01......................CC....... .................O
    DDDDD............03......................DD....... .................O


    select a.ls_id, a.FL_ID,a.RM_TYPE,b.DV_ID,c.COD_RITMI,c.DESCR_RITM I,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'


    In this case ls_id= 008 and 006 has snop='TIM' (not T03).

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What you are asking for makes no sense. You are saying that you want b.snop to equal T03 and you want b.dv_id to be null. Your query will NEVER return any information. If all you want is a listing of records that meet that criteria then issue the following query.

    select *
    from dp_test
    where dv_id is null
    and snop='T03';

    remember that if you have the clause a.dv_id=b.div_id and either or both of the dv_id's are null, the match will be false.
    Last edited by beilstwh; 09-03-04 at 11:52.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    it sure would be nice if you posted EXACTLTY what went into
    sql*plus. This includes what query sqlplus ran and the output from sql*plus. Then tell us how you would like the output different from
    what sql*plus gave.

    instead you give us possible output without the sql statement.
    I can't tell if you cut/pasted from sqlplus or if you made it up.

    Something like this would be nice:
    PHP Code:
    select attribute_id from attributes
      2  where attribute_id 
    5;

    ATTRIBUTE_ID
    -------------
                
    1
                2
                3
                4

    Elapsed
    00:00:00.00 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jul 2002
    Posts
    227
    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 ='DOM'

    I'll have somehing of this:

    LS_ID....FL_ID....RM_TYPE............DV_ID.......C OD_RITMI......DESCR_RITMI......TIPO_SPAZIO
    001.......P01.........AAAAA.............DOM....... .........01.............AA........................ O
    001.......P01.........BBBBB....................... .............02.............BB.................... ....O
    001.......P02.........DDDDD....................... ...........03.............DD...................... ..O

    009.......P01.........CCCCC....................... .............01............CC..................... .......
    009.......P01.........BBBBB...............DOM..... .........02...............BB...................... ..V
    009.......P02.........CCCCC....................... .................01.............CC................ ........O


    Is possible obtain this result?

    Thanks in advance!

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    this?
    PHP Code:
    SELECT a.ls_ida.fl_ida.rm_typeb.dv_idc.cod_ritmic.descr_ritmi,
           
    c.tipo_spazio
      FROM cod_test a
    dp_test bti_use_test c
     WHERE b
    .snop 'T03'
       
    AND a.dv_id b.dv_id(+)
       AND 
    a.rm_type c.rm_type(+)
       AND (
    a.dv_id IS NULL or a.dv_id ='DOM'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •