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_3week s.week_no
FROM (select weighted_avg_traffic.eis_d ate,
decode(to_char(to_date(wei ghted_avg_ traffic.ei s_date,'yy yymmdd'),' d'),'1','S UN','2','M ON','3','T UE','4','W ED','5','T HU','6','F RI','7','S AT') as weekday,
decode(to_number(to_char(t o_date(wei ghted_avg_ traffic.ei s_date,'yy yymmdd')-t o_date(a.e is_date,'y yyymmdd'), '99'))/7,0 ,3,1,2,2,1 ) as week_no,
weighted_avg_traffic.traff ic,48081 as agree,
(weighted_avg_traffic.traf fic/48081) as cpr_agr
from weighted_avg_traffic,
(select eis_date
from weighted_avg_traffic
where eis_date between to_char(sysdate-21,'yyyymm dd') and to_char(sysdate-15,'yyyymm dd')
and to_char(to_date(eis_date,' yyyymmdd') ,'d')='2') a
where weighted_avg_traffic.eis_d ate between a.eis_date and to_char(sysdate-1,'yyyymmd d')
order by 1) wtd_avg_3weeks
WHERE ( wtd_avg_3weeks.weekday='MO N' ) and ( wtd_avg_3weeks.week_no = 1)
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
FROM (select weighted_avg_traffic.eis_d
decode(to_char(to_date(wei
decode(to_number(to_char(t
weighted_avg_traffic.traff
(weighted_avg_traffic.traf
from weighted_avg_traffic,
(select eis_date
from weighted_avg_traffic
where eis_date between to_char(sysdate-21,'yyyymm
and to_char(to_date(eis_date,'
where weighted_avg_traffic.eis_d
order by 1) wtd_avg_3weeks
WHERE ( wtd_avg_3weeks.weekday='MO
ASKER
Same result
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or, change the format mask to '999'
wtd_avg_3weeks.week_no = '1'
Instead of
wtd_avg_3weeks.week_no = 1