-
Urgent!! please help me-- Oracle code
I am working on Oracle 8.0.5 version
I have an fe_asset table.
There is an asset_description column which contains both the description of asset and the quantity.
sometimes the description is as under:
My Current code takes care of this:
PART A
2 tables -- 2 refers the quantity
58 computer books -- 58 refers the computer books.
Insurance books -- Here quantity is implied as 1.
My current code does not take care of these following things:
PART B
Fans (8) -- Here 8 refers quanity
Electroux754 -- Here quanity is 1 , 754 is the brand name
tables and chairs x 48 -- Here 48 refers to quantity.
This is just a sample of the quanity in the asset description column. My work is to pick the quantity and use the quantity to divide the purcahse cost. ie, purchase_cost/quantity. If the per unit cost of an aset is less than 1000 then I have to consider all those assets. This table contains more than 1million records.
I have written a code. Can I use some other code or modify mode my code to include PART B too?
Also, is there any way to find whether it is a char or a number?
***************************
CODE
Declare
Cursor c1 is
select asset_id, asset_description, purchase_cost
from fe.fe_asset;
v_char number(10);
v_char1 number(10);
Begin
delete from padfe4;
for x in c1 loop
Begin
select nvl(to_number(substr(ASSET_DESCRIPTION,1,INSTR (ASSET_DESCRIPTION,' ',1, 1)-1)),1)
into v_char
from fe.fe_asset where asset_id = x.asset_id;
If substr(x.ASSET_DESCRIPTION,1,INSTR (x.ASSET_DESCRIPTION,' ',1, 1)-1) = '0' then
v_char := 1;
If ((x.purchase_cost/v_char) < 1000) or (x.purchase_cost < 1000) then
insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
End if;
elsif ((x.purchase_cost/v_char) < 1000) or (x.purchase_cost < 1000) then
insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
End if;
commit;
Exception
when INVALID_NUMBER then
v_char := 1;
If (x.purchase_cost/v_char) < 1000 then
insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,1);
end if;
commit;
WHEN value_error THEN
dbms_output.put_line('This is a value_error in inner exception'||x.asset_id);
dbms_output.put_line(sqlcode||' '||sqlerrm);
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
End;
End loop;
commit;
Exception
when INVALID_NUMBER then
dbms_output.put_line('This is a char in outer loop');
WHEN value_error THEN
dbms_output.put_line('This is a VALUE_ERROR IN outer loop');
when others then
dbms_output.put_line('This is the last alter in outer loop');
End;
**************************************
2nd piece of code:
I also tried using ascii and working out this way but somewhere I am getting stuck to find a solution for PART B
Declare
Cursor c1 is
select asset_id, asset_description, purchase_cost
from fe_asset;
v_char number(10);
v_char1 number(10);
v_len number(10);
v_ret varchar2(15);
v_num number(10);
p_char varchar2(2);
v_char10 number(10);
v_char11 number(10);
v_char12 number(10);
v_char13 number(10);
v_char14 number(10);
v_char15 number(10);
v_char16 number(10);
v_char17 number(10);
v_char18 number(10);
Begin
delete from padsm;
for x in c1 loop
Begin
If x.purchase_cost < 1000 then
insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,20000);
else
select nvl(to_number(substr(ASSET_DESCRIPTION,1,INSTR (ASSET_DESCRIPTION,' ',1, 1)-1)),1)
into v_char
from fe_asset where asset_id = x.asset_id;
If ((x.purchase_cost/v_char) < 1000) then --or (x.purchase_cost < 1000) then
insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
elsif substr(x.ASSET_DESCRIPTION,1,INSTR (x.ASSET_DESCRIPTION,' ',1, 1)-1) = '0' then
v_char := 1;
If ((x.purchase_cost/v_char) < 1000) THEN--or (x.purchase_cost < 1000) then
insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
End if;
End if;
END IF;
commit;
Exception
when INVALID_NUMBER then
select length(x.ASSET_DESCRIPTION) into v_len from fe_asset
where asset_id = x.asset_id;
for ctr in 1..v_len loop
v_num := ascii(substr(x.ASSET_DESCRIPTION,ctr,1));
if v_num = 48 then
v_char := 0;
elsif v_num = 49 then
v_char10 := 1;
elsif v_num = 50 then
v_char11 := 2;
elsif v_num = 51 then
v_char12 := 3;
elsif v_num = 52 then
v_char13 := 4;
elsif v_num = 53 then
v_char14 := 5;
elsif v_num = 54 then
v_char15 := 6;
elsif v_num = 55 then
v_char16 := 7;
elsif v_num = 56 then
dbms_output.put_line('This is for checking v_char'||v_char||x.asset_id);
v_char17 := 8;
elsif v_num = 57 then
v_char18 := 9;
else p_char := 'C';
end if;
--v_char:= to_number(v_char||v_char10||v_char11||v_char12||v_char13||v_char14||v_char15||v_char16||v_char16||v_ char17);
end loop;
v_char:= to_number(v_char||v_char10||v_char11||v_char12||v_char13||v_char14||v_char15||v_char16||v_char16||v_ char17);
If p_char = 'C' then
v_char := 1;
end if;
If (x.purchase_cost/v_char) < 1000 then
insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
end if;
commit;
WHEN value_error THEN
dbms_output.put_line('This is a value_error in inner exception');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
End;
End loop;
commit;
Exception
when INVALID_NUMBER then
dbms_output.put_line('This is a char in outer loop');
WHEN value_error THEN
dbms_output.put_line('This is a VALUE_ERROR IN outer loop');
when others then
dbms_output.put_line('This is the last alter in outer loop');
End;
******************************
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|