Re: Problem with using NVL Function ins 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'),
>

Try putting the NVL in the most inner brackets. I think your problem is that TO_NUMBER can't handle empty values. Something like: ......... (TO_NUMBER(NVL(TRIM(konten_nr),'0'))........ This wil force at least a 0 into the TO_NUMBER function. Received on Wed Jan 26 2005 - 12:57:08 CET