Re: NVL( ) with NUMBER field

It sounds like you have usiness rules which say either

"takes values from these optional fields and place them in these mandatory field"

Assuming so then either your busniess rule is in error or your database design is. I would generally assume the business rule is as required so think about changing the definition of your audit table. It sounds lik ethe table definiti0ons is wrong fro your rule. The columns should be NULL allowed.

Also sound slike the type is wrong if you have to put VARCHAR2 values into a NUMBER column.

If you can;t do this then you'll have to think about some dummy numeric value to insert instead of a null number. "-1" or something. Some value that will not appear in the table data itself, if you have one.

"Stephen B" <stephen.bell_at_cgi.ca> wrote in message 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 - 07:30:05 CDT