Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question Unanswered: Using Formatting Function NVL

    I ran across a problem with the format of an ASCI output file of an SQL script. The problem I have is with handling a particular column that contains account numbers. The column is defined with 8 characters. However not every entry has data. I have several fields that don't have any information.

    Originally in my script I had the following line that made the script fail:

    Code:
    LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0'),

    I then changed the line to the following: (The script ran but now I notice that the formatting is wrong)

    Code:
    LPAD(TO_NUMBER(LTRIM(konten_nr)),8,'0'),

    I attempted to use the NVL function that will return a value when there is nothing in the column field but it doesn't work. Does anyone know what I'm doing wrong?

    Code:
    LPAD(NVL(TO_CHAR(TO_NUMBER(konten_nr),'FM999999999'),'0'),8,'0'),

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Try putting the NVL around the field directly:
    LPAD(TO_CHAR(TO_NUMBER(nvl(konten_nr,'0')),'FM9999 99999 '),8,'0'),

    Maybe? I am guessing and absolutely not sure....

    Regards

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think you could just use NVL like this :
    Code:
    LPAD(NVL(konten_nr,'00000000'), 8, '0')
    That is you get your account numbers left-padded with zeros up to 8 characters, and get 8 zeros when data is NULL. Is it what you want ?

    Regards,

    RBARAER

  4. #4
    Join Date
    Nov 2004
    Posts
    57

    Thumbs up

    Thanks RBARAER,

    That was the thing. Now my output looks correct. Thanks again.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If the field must have 8 chars or either dont have anything at all ( null ), then the LPAD function on RBARAER's reponse is not needed, IMO.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You're right JMartinez, if the field is either NULL or 8 characters long, then LPAD is not needed. Otherwise, if 8 is just a max size and there are shorter values, but you want the output of your query always to be 8 characters long, left-padded with zeros, then LPAD is needed.

    Regards,

    RBARAER

Posting Permissions

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