Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    20

    Question Unanswered: Getting #### instead of numbers

    My initial query was
    Code:
    select pil_pilotname, sum(fl_distance) "Miles Flown"
    from (pilots left outer join departures
    on pil_pilot_id = dep_pilot_id)
    left outer join flight
    on dep_flight_no = fl_flight_no
    group by pil_pilotname
    order by sum(fl_distance) desc;
    I needed to add text where there weren't any values
    Code:
    select nvl(to_char(sum(fl_distance)), 'Not yet flown') "Miles Flown", pil_pilotname
    from (pilots left outer join departures
    on pil_pilot_id = dep_pilot_id)
    left outer join flight
    on dep_flight_no = fl_flight_no
    group by fl_distance, pil_pilotname
    order by fl_distance desc;
    The new code added 4 extra columns so I need to switch back the my initial query...but the "Miles Flown" now gives ###### instead of actual numbers.

    Any suggestion? Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you manually set this column's format? Such as
    Code:
    SQL> select sum(sal) salary from emp;
    
        SALARY
    ----------
         40225
    
    SQL> column salary format 9999.99        --> this line
    SQL> select sum(sal) salary from emp;
    
      SALARY
    --------
    ########
    
    SQL>
    If so, set format to a more appropriate value.

  3. #3
    Join Date
    Apr 2009
    Posts
    20
    I'll try that. Thanks.

Posting Permissions

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