hello, anybody can help me?
i have problem in dis PL/SQL stuff and i have no idea to solve it again. i'm stuck :'(
d error said :
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at line 71
but i have check all d tables i hv made n all is d same..please help me...
below is d code
DECLARE
CURSOR c1 IS select extno, dialno, sdate, stime, duration, trunk, to_char(sdate, 'DAY') day, dur, detime
from z_pabx;
bill c1%rowtype;
ctype char(1);
cost number(7,2);
unitprice number(7,2);
unitpulse number(3);
airtime number(6,3);
destination varchar2(30);
BEGIN
open c1;
loop
cost := 0;
fetch c1 into bill;
IF c1%NOTFOUND THEN
exit;
ELSE
IF bill.dialno like '001%' THEN
ctype := 'I';
SELECT country_name
INTO destination
FROM z_count
WHERE (to_number(bill.dialno,4,4)= country_code)
OR (to_number(bill.dialno,4,3)= country_code)
OR (to_number(bill.dialno,4,2)= country_code)
OR (to_number(bill.dialno,4,1)= country_code);
IF bill.day = 'SUNDAY' THEN --OR bill.sdate in (SELECT daydate from z_holiday) THEN
SELECT trfred
INTO unitprice
FROM z_count;
cost := ceil(bill.dur/60)*unitprice;
ELSE
SELECT B.trf
INTO unitprice
FROM z_count A, z_cgroupt B
WHERE A.grp_trf = B.grp_trf
AND bill.detime
BETWEEN to_number(B.stime )AND to_number(B.etime);
cost := ceil(bill.dur/60)*unitprice;
END IF;
ELSIF bill.dialno like '016%' THEN
ctype := 'V';
IF substr(bill.dialno,4,2) like '08%' THEN
SELECT city
INTO destination
FROM z_cellzone
WHERE to_number(bill.dialno,6,2)= cell_code
AND to_number(bill.dialno,8,6)
BETWEEN range1 AND range2;
cost := ceil(bill.dur/60)*2300;
ELSIF substr(bill.dialno,4,1) like '0%' THEN
SELECT city_name
INTO destination
FROM z_city
WHERE to_number(bill.dialno,5,3)= city_code OR to_number(bill.dialno,5,2)= city_code;
cost := ceil(bill.dur/60)*1500;
ELSE
SELECT dest
INTO destination
FROM z_local
WHERE to_number(bill.dialno,1,3) = pref2
OR to_number(bill.dialno,1,2)= pref2;
cost := ceil(bill.dur/60)*1500;
END IF;
ELSIF bill.dialno like '0809%' THEN
ctype := 'N';
cost := ceil(bill.dur/3600)*9750;
ELSIF bill.dialno like '0%' THEN
ctype := 'D';
SELECT C.city_name
INTO destination
FROM z_city C, z_zoning D
WHERE to_number(bill.dialno,2,3) = C.city_code OR to_number(bill.dialno,2,2) = C.city_code
AND C.zone = D.zone;
IF bill.day = 'SUNDAY' THEN
SELECT pulse, rpp
INTO unitpulse, unitprice
FROM z_zoning
WHERE bill.detime
BETWEEN to_number(fhour) AND to_number(lhour)
AND holdflag = 1;
cost := ceil(bill.dur/unitpulse)*unitprice;
ELSE
SELECT pulse, rpp
INTO unitpulse, unitprice
FROM z_zoning
WHERE bill.detime
BETWEEN to_number(fhour) AND to_number(lhour)
AND holdflag = 0;
cost := ceil(bill.dur/unitpulse)*unitprice;
END IF;
ELSIF bill.dialno like '08%' THEN
ctype := 'C';
SELECT A.city
INTO destination
FROM z_cellzone A, z_cellt C
WHERE to_number(bill.dialno, 2, 3) = A.cell_code
AND (to_number(bill.dialno,5,7) BETWEEN A.range1 AND A.range2)
OR (to_number(bill.dialno,5,6) BETWEEN A.range1 AND A.range2)
AND A.zone = C.zone;
IF bill.day = 'SUNDAY' THEN --OR B.sdate in D.daydate THEN
SELECT pulse, rpp, airtimepp
INTO unitpulse, unitprice, airtime
FROM z_cellt
WHERE bill.detime
BETWEEN to_number(stime) AND to_number(etime)
AND holflag = 1;
cost := ((ceil(bill.dur/unitpulse)*unitprice) + (ceil(bill.dur/unitpulse)*airtime));
ELSE
SELECT pulse, rpp, airtimepp
INTO unitpulse, unitprice, airtime
FROM z_cellt
WHERE bill.detime
BETWEEN to_number(stime) AND to_number(etime)
AND holflag = 0;
cost := ((ceil(bill.dur/unitpulse)*unitprice) + (ceil(bill.dur/unitpulse)*airtime));
END IF;
ELSE
ctype := 'L';
SELECT A.dest
INTO destination
FROM z_local A, z_zoning B
WHERE to_number(bill.dialno,1,5)
BETWEEN A.pref1 AND A.pref2
AND A.zone = B.zone;
IF bill.day = 'SUNDAY' THEN --OR bill.sdate = z_holiday.daydate THEN
SELECT pulse, rpp
INTO unitpulse, unitprice
FROM z_zoning
WHERE bill.detime
BETWEEN to_number(fhour) AND to_number(lhour)
AND holdflag = 1;
cost := ceil(bill.dur/unitpulse)*unitprice;
ELSE
SELECT pulse, rpp
INTO unitpulse, unitprice
FROM z_zoning
WHERE bill.detime
BETWEEN to_number(fhour) AND to_number(lhour)
AND holdflag = 0;
cost := ceil(bill.dur/unitpulse)*unitprice;
END IF;
END IF;
END IF;
insert into z_bin (JASA,EXTNO,DIALNO,SDATE,STIME,DURATION,COST)
values(ctype,bill.extno,bill.dialno,bill.sdate,bill.stime,bill.duration, cost);
end loop;
commit;
close c1;
END;
thanx 4 ur attention, i really appreciated it
