Results 1 to 5 of 5

Thread: oracle reports

  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: oracle reports

    how can i make null values of an address not be included in the address;

    some people don;t have address 2 and address3, when their statement is printed out both these fields are BLANK...

    for example
    Joe bloggs
    new street


    london
    ec1

    the address fields only get populated when there is a address 2 & address 3




    ---------


    select:
    INITCAP(ecdv.address1) address1,
    INITCAP(ecdv.address2)address2,
    INITCAP(ecdv.city) city,
    INITCAP(ecdv.county) county,
    UPPER(ecdv.postal_code) postal_code,
    INITCAP(ecdv.country) country,
    INITCAP(ecdv.full_name) ,
    eurostar_get_trans_string(ecdv.incentive_level ,NVL(hzp.attribute17, 'GB')),


    ----
    INITCAP(ecdv.address1) ,
    INITCAP(ecdv.address2),
    INITCAP(ecdv.city) ,
    INITCAP(ecdv.county),
    UPPER(ecdv.postal_code) ,
    INITCAP(ecdv.country) ,


    ------------------------
    Cheers
    Etravels

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    you can have a format trigger. Something like if address2 is null then return false.
    As such, I hate such things. They makes the report "slow"

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: oracle reports

    can you do like this:

    sellect
    address1||decode(address2,null,null,chr(10))||addr ess2 adress
    ...

    make address one field instead of two



    Originally posted by etravels
    how can i make null values of an address not be included in the address;

    some people don;t have address 2 and address3, when their statement is printed out both these fields are BLANK...

    for example
    Joe bloggs
    new street


    london
    ec1

    the address fields only get populated when there is a address 2 & address 3




    ---------


    select:
    INITCAP(ecdv.address1) address1,
    INITCAP(ecdv.address2)address2,
    INITCAP(ecdv.city) city,
    INITCAP(ecdv.county) county,
    UPPER(ecdv.postal_code) postal_code,
    INITCAP(ecdv.country) country,
    INITCAP(ecdv.full_name) ,
    eurostar_get_trans_string(ecdv.incentive_level ,NVL(hzp.attribute17, 'GB')),


    ----
    INITCAP(ecdv.address1) ,
    INITCAP(ecdv.address2),
    INITCAP(ecdv.city) ,
    INITCAP(ecdv.county),
    UPPER(ecdv.postal_code) ,
    INITCAP(ecdv.country) ,


    ------------------------

  4. #4
    Join Date
    Feb 2004
    Posts
    93

    Re: oracle reports

    Originally posted by lynden.zhang
    can you do like this:

    sellect
    address1||decode(address2,null,null,chr(10))||addr ess2 adress
    ...

    make address one field instead of two
    hiya

    that doesn't work properly:
    how can I set up a function/trigger so that if Address 2 and address3 are null to skip and jump to city, without having the 'BLANK' fields shown:

    currently I have for the trigger:

    Function f_address3formattrigger0009 return boolean is
    begin
    return (true)
    end:

    but i would like it to say something like

    if address2 is null,
    then .....
    else
    if address3 is null then....
    return ..
    ......how can this worl...

    cheers
    Cheers
    Etravels

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just like pinakdb told you ... for every field you have to create a trigger which would have something like:
    PHP Code:
    FUNCTION f_address3_formattrigger
       
    RETURN BOOLEAN
    IS
    BEGIN
       
    IF :address3 IS NULL
       THEN
          
    RETURN (FALSE);
       ELSE
          RETURN (
    TRUE);
       
    END IF;
    END
    You can NOT include all of IF ... THEN ... ELSE conditions into one trigger.

Posting Permissions

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