Re: Problem with using NVL Function in SQL

paul.izzo_at_mosca-ag.com wrote:
> 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:
>
> LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0'),
>
> I then changed the line to the following: [COLOR=Blue](The script ran
> but now I notice that the formatting is wrong)[/COLOR]
>
> 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?
>
>
> LPAD(NVL(TO_CHAR(TO_NUMBER(konten_nr),'FM999999999'),'0'),8,'0'),

lpad(nvl(konten_nr, '0'), 8, '0')
You can wrap it inside trim functions.

Not sure why you are doing a to_char(to_number()) on a char field. Looks like an overkill.

Regards
/Rauf Received on Wed Jan 26 2005 - 04:34:36 CST