Link to home
Start Free TrialLog in
Avatar of iamjhkang
iamjhkang

asked on

RE: ORA-01722:invalid number

I have a query which collects the traffic of recent 3 weeks.
To my disappointment, I got a "ORA-01722:invalid number" error message.

One thing strange is when I omit the condition "( wtd_avg_3weeks.week_no = 1)" it works ok like;

CPR_AGR,TRAFFIC,WEEK_NO
0.486553940225869,23394,3
0.465235748008569,22369,2
0.415735945591814,19989,1

I use Oracle 8.1.7.
How can I solve this problem?

TIA.

The query is as follows.

SELECT wtd_avg_3weeks.cpr_agr, wtd_avg_3weeks.traffic,wtd_avg_3weeks.week_no
FROM (select weighted_avg_traffic.eis_date,
             decode(to_char(to_date(weighted_avg_traffic.eis_date,'yyyymmdd'),'d'),'1','SUN','2','MON','3','TUE','4','WED','5','THU','6','FRI','7','SAT') as weekday,
             decode(to_number(to_char(to_date(weighted_avg_traffic.eis_date,'yyyymmdd')-to_date(a.eis_date,'yyyymmdd'),'99'))/7,0,3,1,2,2,1) as week_no,
             weighted_avg_traffic.traffic,48081 as agree,
             (weighted_avg_traffic.traffic/48081) as cpr_agr
        from weighted_avg_traffic,
            (select eis_date
               from weighted_avg_traffic
              where eis_date between to_char(sysdate-21,'yyyymmdd') and to_char(sysdate-15,'yyyymmdd')
                and to_char(to_date(eis_date,'yyyymmdd'),'d')='2') a    
       where weighted_avg_traffic.eis_date between a.eis_date and to_char(sysdate-1,'yyyymmdd')
       order by 1) wtd_avg_3weeks
WHERE ( wtd_avg_3weeks.weekday='MON' ) and  ( wtd_avg_3weeks.week_no = 1)

Avatar of grim_toaster
grim_toaster

What's the datatype of the wtd_avg_3weeks.week_no column?  If it is varchar2 then ensure that you have no non-numeric data in there (i.e. a space).  If you want a quick test to see if that could be the problem, try:
wtd_avg_3weeks.week_no = '1'
Instead of
wtd_avg_3weeks.week_no = 1
Avatar of iamjhkang

ASKER

Same result
ASKER CERTIFIED SOLUTION
Avatar of grim_toaster
grim_toaster

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or, change the format mask to '999'