DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Urgent!! please help me-- Oracle code

  1. #1
    Join Date
    Jul 2002
    Posts
    8

    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;

    ******************************

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    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
  •  


Click Here to Expand Forum to Full Width