This discussion is archived
9 Replies Latest reply: Mar 20, 2013 5:11 AM by 703143 RSS

Invalid number error

703143 Newbie
Currently Being Moderated
we are using select query which returns data over DBlink from another oracle database. In where clause we have used IN condition for dates, when we put 2 dates

in where clause it works fine, as we put third date it start giving error invalid number. kindly check following query and let me know the problem.

select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_INfrom ops_swipe_details
where to_number(pers_no) = 651594 and swipe_date in('13-MAR-2013','14-MAR-2013','15-MAR-2013')
;

structure of ops_swipe_details is as follows

create table OPS_SWIPE_DETAILS
(
pers_no VARCHAR2(8) not null,
swipe_indc VARCHAR2(5),
swipe_date DATE,
in_time TIMESTAMP(6),
out_time TIMESTAMP(6),
shift VARCHAR2(1),
in_shift VARCHAR2(1),
regularization_indc VARCHAR2(1),
disc_act_indc VARCHAR2(1),
pers_area VARCHAR2(4),
co_code VARCHAR2(4),
status VARCHAR2(1),
inserted_by VARCHAR2(8),
inserted_on DATE,
transaction_id NUMBER,
timeoff_indc VARCHAR2(1),
submission_of_outpass VARCHAR2(1),
outpass_indc VARCHAR2(1),
reason_code VARCHAR2(4)
)
  • 1. Re: Invalid number error
    jeneesh Guru
    Currently Being Moderated
    It seems there are some Non-Numeric value for PERS_NO for SWIPE_DATE=15ThMarch

    Can you try these queries?
    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN
    from ops_swipe_details
    where to_number(pers_no) = 651594 
    and swipe_date = to_date('15-MAR-2013','DD-MON-YYYY')
    ;
    
    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN
    from ops_swipe_details
    where pers_no = '651594 '
    and swipe_date = to_date('15-MAR-2013','DD-MON-YYYY')
    ;
    Between, if PERS_NI is actually a numeric value, why are storing it in VARCHAR2 column?
  • 2. Re: Invalid number error
    S10390 Journeyer
    Currently Being Moderated
    There may be an alphanumeric value in PERS_NO for '15-MAR-2013'...!!

    Edited by: Santhosh on Mar 20, 2013 3:00 PM
  • 3. Re: Invalid number error
    Paul Horth Expert
    Currently Being Moderated
    To add to Jeneesh's reply never compare dates to strings. swipe_date is a DATE type but '13-MAR-2013' etc. are strings.

    This can cause two main problems:

    1. It will stop the use of any index on the DATE column (if there is one).
    2. If the default date format is different on another machine and you run this, it will fail because the date format is wrong.

    Always wrap strings like that with to_date and a format mask.
  • 4. Re: Invalid number error
    703143 Newbie
    Currently Being Moderated
    I have checked running both the queries given in this post, both works fine. there is no result for second query

    But if i Run this query

    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where pers_no = '651594' , it gives me all 377 records with proper to_number(pers_no) column.

    but when i use to_number in where clause it fetches first 200 records and then says Invalid number

    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where to_number(pers_no) = 651594
  • 5. Re: Invalid number error
    703143 Newbie
    Currently Being Moderated
    there is no alphanumeric value in pers_no
  • 6. Re: Invalid number error
    703143 Newbie
    Currently Being Moderated
    ok if i run this query

    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where to_number(pers_no) = 651594
    and swipe_date between to_date('14-MAR-2013','DD-MON-YYYY') and to_date('14-MAR-2013','DD-MON-YYYY')

    it gives proper result

    also if i run this query

    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where to_number(pers_no) = 651594
    and swipe_date between to_date('15-MAR-2013','DD-MON-YYYY') and to_date('15-MAR-2013','DD-MON-YYYY')

    this also gives proper result

    and if check between 14 and 15 it says Invalid Number
    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where to_number(pers_no) = 651594
    and swipe_date between to_date('14-MAR-2013','DD-MON-YYYY') and to_date('15-MAR-2013','DD-MON-YYYY')
  • 7. Re: Invalid number error
    chris227 Guru
    Currently Being Moderated
    user11193738 wrote:
    there is no alphanumeric value in pers_no
    run the below to find out
    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,pers_no
    , translate(pers_no, 'a1234567890', 'a') no_digit
    from ops_swipe_details
    where
    translate(pers_no, 'a1234567890', 'a') is not null
  • 8. Re: Invalid number error
    chris227 Guru
    Currently Being Moderated
    user11193738 wrote:
    ok if i run this query

    select SWIPE_DATE, OUT_TIME SWIPE_OUT, IN_TIME SWIPE_IN,to_number(pers_no)
    from ops_swipe_details
    where to_number(pers_no) = 651594
    and swipe_date between to_date('14-MAR-2013','DD-MON-YYYY') and to_date('14-MAR-2013','DD-MON-YYYY')
    This kind of predicate makes not much sense, if time componentes play a role.
    You are looking for values between 14-MAR-2013 00:00:00 and 14-MAR-2013 00:00:00.
    If you want to serach for the whole day you may try
    and swipe_date >= to_date('14-MAR-2013','DD-MON-YYYY')
    and swipe_date < to_date('15-MAR-2013','DD-MON-YYYY')
    
    or
    trunc(swipe_date)= to_date('14-MAR-2013','DD-MON-YYYY') 
  • 9. Re: Invalid number error
    703143 Newbie
    Currently Being Moderated
    Thanks Chris, the problem is resolved now. The translate function was useful to us. it identified the nonnumeric values.

    appericate your technical pin point which hit the problem and solved the problem.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points