Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Mexico
    Posts
    5

    Unanswered: to_number question

    Hello! I'm havingo some trouble with this function ... in line 4 the register is Char, so, I'm using the to_number function so I can compare it to 500 (< 500) but the error keeps showing ORA-01722: invalid number... can anyone help me?

    select count(*)
    from sortest S1, so1test S2
    where S1.sortest_tesc_code = 'E07T'
    and to_number(S1.sortest_test_score) < 500 ***HERE
    and S1.sortest_test_date in
    (select MAX(S3.sortest_test_date)
    from sortest S3
    where S3.sortest_pidm = S1.sortest_pidm
    )
    and S2.so2exam_code = 70
    and S1.sortest_pidm = S2.sortest_pidm;

    Thx!

    Yola

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: to_number question

    Can you display the table description for both tables?

  3. #3
    Join Date
    Oct 2003
    Location
    Mexico
    Posts
    5

    Re: to_number question

    You mean this?

    SQL> desc sortest
    Name Null? Type
    ------------------------------- -------- ----
    SORTEST_PIDM NUMBER(8)
    SORTEST_TESC_CODE VARCHAR2(4)
    SORTEST_TEST_SCORE VARCHAR2(5)
    SORTEST_TEST_DATE DATE
    SORTEST_ACTIVITY_DATE DATE

    SQL> desc so1test
    Name Null? Type
    ------------------------------- -------- ----
    SORTEST_PIDM NUMBER(8)
    SORTEST_TESC_CODE VARCHAR2(4)
    SORTEST_TEST_DATE DATE
    STVCAMP_CODE VARCHAR2(3)
    SO2CALE_CODE NUMBER(8)
    SO2EXAM_CODE NUMBER(11)
    SO1TEST_ACTIVITY_DATE DATE

  4. #4
    Join Date
    Oct 2003
    Posts
    71
    What is
    500 ***HERE

    in the line 4 of your select stmt.

  5. #5
    Join Date
    Oct 2003
    Location
    Mexico
    Posts
    5
    Oh I just mean that there's where I'm using the TO_NUMBER function =)

  6. #6
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by yolayebra
    Oh I just mean that there's where I'm using the TO_NUMBER function =)
    What values do you have for
    S1.sortest_test_score

  7. #7
    Join Date
    Oct 2003
    Location
    Mexico
    Posts
    5
    They're TOEFL results, so there must be numbers such as 500, 470, 640, etc. But they're CHARs. I want to get something like, how many people get less than 500, between 500 and 550, more than 550.

  8. #8
    Join Date
    Oct 2003
    Posts
    71
    Originally posted by yolayebra
    They're TOEFL results, so there must be numbers such as 500, 470, 640, etc. But they're CHARs. I want to get something like, how many people get less than 500, between 500 and 550, more than 550.

    I donot see anything wrong with the query unless you have some values
    in that column which have string or spaces in them

  9. #9
    Join Date
    Oct 2003
    Location
    Mexico
    Posts
    5
    I've already tryed with smaller queries like this:

    select to_number(sortest_test_score)
    from sortest
    where sortest_tesc_code = 'E07T'

    I get a list like this and NO error

    TO_NUMBER(SORTEST_TEST_SCORE)
    ------------------------------------------------------
    550
    581
    645

    etc...

    ****
    If I try this

    select to_number(sortest_test_score)
    from sortest
    where sortest_tesc_code = 'E07T'
    and to_number(sortest_test_score) > 500;

    and I get

    Error:ORA-01722: Invalid number

    followed by
    no rows seected

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I would suggest wrapping the column with a trim and an NVL incase there are spaces or something throwing it off.


    select to_number(sortest_test_score)
    from sortest
    where sortest_tesc_code = 'E07T'
    and to_number(nvl(trim(sortest_test_score),0)) > 500;


    The only other thing I can think of would be a character is in the field other than a numeric.

Posting Permissions

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