Re: NVL( ) with NUMBER field

Hi,

You can put character into a numeric field if the character is a number ( '0' , '-1' ).
Can you do NVL(field,-999) ? These will work right: if the final field is character, it will be '-999' and if it is a number, it will be -999. This do you must mark as error the -999 instead of ' '.

Regards,

                                                    Isa

"Stephen B" <stephen.bell_at_cgi.ca> escribió en el mensaje news:mGcr9.4694$Lb1.754400_at_news20.bellglobal.com...
> Good day all,
>
> Oracle 8.1.7.3 on NT 4.
>
> I've been given a trigger to debug...
> It was encountering NO_DATA_FOUND errors due to several SELECT...INTO
> statements where the fields of a non-related table are
> selected into scalar variables after insert. These values are then placed
> into an audit table.
>
> Several of these fields are optional and in fact are often null which
caused
> the no_data_found problem.
>
> I'm thinking I can just use NVL(field_name,' ') to insert an empty string
> into the audit table when there is no data to select into a variable. If
you
> have a better plan please let me know.
>
> Even if "Plan A" above works though, some of the fields are NUMBER
> fields..so, even if I use NVL(TO_CHAR(number_field),' ')
> I think I'm going to run into a problem trying to insert the empty string
> into the number field...is there some other character I should look at
that
> would allow me to use NVL with a number field ?
>
> If I'm headed in the wrong direction entirely please let me know.
>
> Thanks and best regards in advance,
> Steve
>
>
Received on Wed Oct 16 2002 - 10:17:27 CDT