Re: SQL*Loader question

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Tue, 23 Mar 1999 19:46:20 +0100
Message-ID: <36f7e184$0$25410_at_pascal>


shjejurkar wrote
> If the value (number) in one of the fields is more than 9999.9999, the
> corresponding column should be loaded with 0 (zero) instead of the
> original number in the field.

Just add any SQL statement after the corresponding column, enclosed in double quotes, like:

    my_column decimal external

        "decode(sign(9999.9999 - :my_column), -1, 0, :my_column)"

Note the colon prefexing :my_column. Sign(x) yields -1 if x < 0, 0 if x=0, and +1 if x>0. So the decode(..) would return 0 if :my_column exceeds 9999.9999.

Or, better, to avoid rounding problems:

    my_column decimal external

        "decode(sign(10000 - :my_column), 1, :my_column, 0)"

If my_column is 10000 then Sign(0) yields 0, so the decode returns 0. If my_column > 10000 then Sign(x) returns -1, which also tells decode to return 0.

Note that if your refer to :my_column in another part of your loader script, you will be dealing with the value as found in your data file, not with the value that will be loaded into the database. So, if the code above would load my_column with zero, then still in another part you would get the original value:

    my_total char

        ":my_column + :my_other_column"

Of course, if values exceeding 10000 should always be set to zero, then a database trigger could do the job as well:

    create or replace trigger biu_my_table

        before insert or update on my_table
        for each row
    as
        if :new.my_column >= 10000 then
            :new.my_column := 0;
        end if;

    end biu_my_table;
    /
    show errrors

Arjan. Received on Tue Mar 23 1999 - 19:46:20 CET

Original text of this message