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;
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');
The best way is to make a function for the next code:
I assume the amount is in a word(seperated by spaces)
clear
set serveroutput on
declare
--ls_str varchar2(200) := 'Fans (8) ';
ls_str varchar2(200) := 'Electroux754 ';
ls_word varchar2(200);
ls_rest varchar2(200);
i_strt integer := 1;
i_pos integer;
b_exit boolean := false;
ln_amount number(4);
begin
-- Assume the value is in a word, so check all words
-- First remove the head and trail spaces
ls_rest := trim(ls_str);
loop
-- Find a the first space
i_pos := instr(ls_rest,' ') - 1;
if i_pos <= 0
then
-- There are no spaces (anymore), so this is the last word
b_exit := true;
ls_word := ls_rest;
else
-- Word found
ls_word := substr(ls_rest,1,i_pos);
end if;
-- Strip the brackets
if substr(ls_word,1,1) = '('
then
ls_word := replace(ls_word,'(','');
ls_word := replace(ls_word,')','');
end if;
i_strt := i_pos + 2;
ls_rest := substr(ls_rest,i_strt);
begin
ln_amount := to_number(ls_word);
exception
when VALUE_ERROR
then
ln_amount := null;
end;
if b_exit then exit; end if;
end loop;
if ln_amount is null
then
ln_amount := 1;
end if;
dbms_output.put_line( 'Number :'||ln_amount);
end;
/
Reply With Quote
Bookmarks