Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: Convert Oracle proc. to SQL Server Procedure

    Hi,
    Can any one change this oracle proc. to SQL Server procedure.

    Any help will be appreciated.


    PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
    Tot_work_all number(12,2);
    Bid_tot number(12,2);
    Ewo number(12,2);
    Overruns number(12,2);
    Underruns number(12,2);
    Contr_tot_all number(12,2);
    sContractType ae_contract.contr_type%type;
    BEGIN
    select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
    into Tot_work_all
    from valid_item
    Where db_contract = db_id;
    Select sum(Contq * Contr_Price) into Bid_tot
    From Valid_item
    Where nvl(New_Item,'N') <> 'Y'
    and db_contract = db_id;
    Select sum(Qtd * Contr_price) into Ewo
    From Valid_item
    Where nvl(New_item,'N') = 'Y'
    and db_contract = db_id;
    Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
    From Valid_item
    Where Qtd > Nvl(Projq,0)
    and db_contract = db_id
    and nvl(New_Item,'N') = 'N';
    IF LAT_type <> 'R' THEN
    Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
    From Valid_item
    Where Nvl(Projq,0) < Contq
    and db_contract = db_id
    and nvl(New_Item,'N') = 'N';
    ELSE
    Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
    From Valid_item
    Where Nvl(Qtd,0) < Contq
    and db_contract = db_id
    and nvl(New_Item,0) = 'N';
    end if;
    Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
    +NVL(underruns,0);

    IF Contr_tot_all = 0 THEN

    Select Contr_type into sContractType from ae_contract where db_contract = db_id;

    IF sContractType = 'A' OR sContractType = 'T' THEN
    --If the divisor is zero here, it's not an error.
    update ae_contract set perc_compu = 0 where db_contract = db_id;

    ELSE
    --If the divisor is zero here, it would be an error
    update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
    END IF;
    Else
    --Here we have a real number to calculate, so go ahead and do your stuff!
    update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
    END IF;
    END;
    mr_roomi

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    be patient i'm gonna try to work this tonight

    but in the meantime if you could provide some table ddl, that would be good.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Some information about [http://vyaskn.tripod.com/oracle_sql_...quivalents.htm] task.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •