Problem with using NVL Function in SQL

From: <paul.izzo_at_mosca-ag.com>
Date: 26 Jan 2005 01:26:10 -0800
Message-ID: <1106731570.001633.299640@c13g2000cwb.googlegroups.com>

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'), Received on Wed Jan 26 2005 - 03:26:10 CST