Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: decode for NULL values

    can someone tell me how to use the DECODE & NVL for addresss2 & address3 in
    sql in the following:

    I want to use the above so that when I am printing out statements which
    address2 & address3 are blank (some customers do not have them, while some
    have) and I want to tell the code to skip to the next field which has values
    so when I print out the report you will not see blank lines in the address.?

    Address1 (OK)
    address2(
    address3
    city (OK)
    county (OK)
    postalcode OK)

    so if you print out the above and no address or NUll values the adddress
    print out will have blank spaces, ie

    address1


    City
    Country
    Postalcode
    Cheers
    Etravels

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: decode for NULL values

    Originally posted by etravels
    can someone tell me how to use the DECODE & NVL for addresss2 & address3 in
    sql in the following:

    I want to use the above so that when I am printing out statements which
    address2 & address3 are blank (some customers do not have them, while some
    have) and I want to tell the code to skip to the next field which has values
    so when I print out the report you will not see blank lines in the address.?

    Address1 (OK)
    address2(
    address3
    city (OK)
    county (OK)
    postalcode OK)

    why dont u try these:

    select address1, nvl2(address2,address,' ') from table_name

    so if you print out the above and no address or NUll values the adddress
    print out will have blank spaces, ie

    address1


    City
    Country
    Postalcode
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Etravels, don't you read your previous topics?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    However, it can be done using DECODEs, but makes your code awful. I was forced to do so writing reports in Oracle Report Writer 1.1, but using Reports 6/9 it is absolutely unnecessary (that's, however, my opinion ...).

    Code using DECODEs as you'd like to have in my reports looked like this (I left all the names in Croatian; this code is just to show how bloody this is):
    PHP Code:
    SELECT
    -- ADRESA DOSTAVE ULICA
           DECODE 
    (pl.tip_platioc,
                   
    1DECODE (pl.sif_ulice,
                              
    '000000'mpl.naz_mjesta
                               
    || DECODE (pl.br_kuce,
                                          
    NULLNULL,
                                          
    ' ' || pl.br_kuce
                                         
    ),
                                 
    upl.naz_ulice
                              
    || DECODE (pl.br_kuce,
                                         
    NULLNULL,
                                         
    ' ' || pl.br_kuce
                                        
    )
                             ),
                   
    5DECODE (pl.sif_ulice,
                              
    '000000'mpl.naz_mjesta
                               
    || DECODE (pl.br_kuce,
                                          
    NULLNULL,
                                          
    ' ' || pl.br_kuce
                                         
    ),
                                 
    upl.naz_ulice
                              
    || DECODE (pl.br_kuce,
                                         
    NULLNULL,
                                         
    ' ' || pl.br_kuce
                                        
    )
                             ),
                   
    DECODE (po.sif_ulice,
                           
    '000000'mpo.naz_mjesta
                            
    || DECODE (po.br_kuceNULLNULL' ' || po.br_kuce),
                              
    upo.naz_ulice
                           
    || DECODE (po.br_kuceNULLNULL' ' || po.br_kuce)
                          )
                  ) 
    dostava_ulica,
           
    --
    -- 
    ADRESA DOSTAVE MJESTO
           DECODE 
    (pl.tip_platioc,
                   
    1DECODE (pl.sif_ulice,
                              
    '000000'pttpl.br_poste || ' ' || pttpl.naz_poste,
                              
    DECODE (mpl.naz_mjesta,
                                      
    pttpl.naz_postepttpl.br_poste || ' '
                                       
    || pttpl.naz_poste,
                                      
    mpl.naz_mjesta
                                     
    )
                             ),
                   
    5DECODE (pl.sif_ulice,
                              
    '000000'pttpl.br_poste || ' ' || pttpl.naz_poste,
                              
    DECODE (mpl.naz_mjesta,
                                      
    pttpl.naz_postepttpl.br_poste || ' '
                                       
    || pttpl.naz_poste,
                                      
    mpl.naz_mjesta
                                     
    )
                             ),
                   
    DECODE (po.sif_ulice,
                           
    '000000'pttpo.br_poste || ' ' || pttpo.naz_poste,
                           
    DECODE (mpo.naz_mjesta,
                                   
    pttpo.naz_postepttpo.br_poste || ' '
                                    
    || pttpo.naz_poste,
                                   
    mpo.naz_mjesta
                                  
    )
                          )
                  ) 
    dostava_mjesto,
           
    --
    -- 
    ADRESA DOSTAVE POSTA
           DECODE 
    (pl.tip_platioc,
                   
    1DECODE (pl.sif_ulice,
                              
    '000000'NULL,
                              
    DECODE (mpl.naz_mjesta,
                                      
    pttpl.naz_posteNULL,
                                      
    pttpl.br_poste || ' ' || pttpl.naz_poste
                                     
    )
                             ),
                   
    5DECODE (pl.sif_ulice,
                              
    '000000'NULL,
                              
    DECODE (mpl.naz_mjesta,
                                      
    pttpl.naz_posteNULL,
                                      
    pttpl.br_poste || ' ' || pttpl.naz_poste
                                     
    )
                             ),
                   
    DECODE (po.sif_ulice,
                           
    '000000'NULL,
                           
    DECODE (mpo.naz_mjesta,
                                   
    pttpo.naz_posteNULL,
                                   
    pttpo.br_poste || ' ' || pttpo.naz_poste
                                  
    )
                          )
                  ) 
    dostava_posta
      FROM 
    ...
      
    WHERE ... 
    I'd suggest you to use format triggers in Report Builder ...
    Last edited by Littlefoot; 04-06-04 at 08:51.

  5. #5
    Join Date
    Feb 2004
    Posts
    93

    thanks littlefoot

    yes I know i put posted '2' very similar topics on this site within the past 2 days but I tried using the last topic in a different forum (it toolbox) which is rubbish...and I wanted to see how the results would differ...

    YOur replies are good.....my knowledge is getting better and better.....all help is very much appreciated

    Em
    Cheers
    Etravels

Posting Permissions

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