Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: limiting an implicit type conversion

    Hello, I suffer from working with a database with not enough constraints. I have a varchar2(50) field that holds a date. In theory, the 1st 4 characters of this field are the year. If some clown hasn't entered his or her data properly, I end up with a ORA-01722 Invalid number error. Please withhold comments about holding a date in a varchar2.

    Anyhoo, I have this function that I found that checks if those characters are actually numbers, but if I limit my output to only valid years, Oracle doesn't seem to care. It checks the whole column for the implicit cast and gives me ORA-01722. I even went so far as to limit the original table in a separate view (see below). Any ideas?

    Thanks,
    Steve


    create or replace function ISNUMBER(in_var in varchar2)
    return number
    is
    v_number number;
    begin
    begin
    v_number := in_var;
    v_number := 0; -- Number...
    exception
    when value_error then
    v_number := 1; -- Not a number..
    end;
    return( v_number );
    end;
    /

    CREATE OR REPLACE VIEW VALID_DATE_VW AS
    SELECT PRIMARY_ID, FORIEGN_ID, CRAPPY_DATE FROM TABLE_A
    WHERE ISNUMBER(SUBSTR(CRAPPY_DATE,1,4)) = 0;

    CREATE OR REPLACE VIEW A_COUNT_PRE_1900 AS
    select count(primary_id) primary_count, foreign_id from table_a
    WHERE SUBSTR(CRAPPY_DATE,1,4) < 1900
    group by foreign_id;

  2. #2
    Join Date
    Oct 2004
    Posts
    145
    Hi Steve,

    I am not clear as to what you are attempting to do.

    Are you simply trying to pull out bad data? Is the issue, 2 digit year versus 4 digit year? If the issue is simply 2 digit versus 4 digit and remainder of data is the same. Pull out the data where length is should be length - 2.

    Without know what data is being stored, simply looking for first digit of 0 or 1 meet your requirement for date validation?

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    I'm trying to eliminate records with bogus dates from the count so I don't get the ORA- error.

    Steve

  4. #4
    Join Date
    Oct 2004
    Posts
    145
    I can not remember the except syntax but try capturing the error number in the exception

    I think it is
    when OERR = 01722 then

    You may need to change
    v_number := in_var;
    to
    v_number := to_number(in_var);
    for your function to generate the error code.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How about just checking for a bad date in the first place.
    This assums that the date is stored in the format YYYYMMDD, if it is different , adjust the function

    create or replace function ISDATE(in_var in varchar2)
    return number
    is
    v_date date;
    begin
    v_date := to_date(in_var,'YYYYMMDD');
    RETURN(0);
    exception
    return(-1 );
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Dec 2004
    Posts
    3
    Bill, You inspired me to discover this solution:

    create or replace function GETYEAR(in_var in varchar2)
    return number
    is
    v_year number;
    begin
    begin
    v_year := substr(in_var,1,4);
    exception
    when value_error then
    return(-1);
    end;
    RETURN(v_year);
    end;
    /

    CREATE OR REPLACE VIEW A_COUNT_PRE_1900 AS
    select count(primary_id) primary_count, foreign_id from table_a
    WHERE GETYEAR(CRAPPY_DATE) < 1900 AND
    GETYEAR(CRAPPY_DATE) <> -1
    group by foreign_id;

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •