-
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;
******************************
-
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;
/
Regards
Ben de Boer
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
|