Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Missing right parenthesis error

    Hi ALL,

    I am new to this forum. Don't know where to put this issue.

    I am writing a function as below -


    create or replace
    function ATOS_GET_GDC_ROLLUP (p_gdc in varchar, p_year in number, p_userid in number, p_gdcpara in varchar) return ATOS_CAPACITY_ROLLUP_TYPE_SI
    as

    v_capacity_rollup_si ATOS_CAPACITY_ROLLUP_TYPE_SI;
    v_name varchar2(255);
    v_month varchar2(20);
    v_budget number;
    v_supply number;
    v_pipeline number;
    v_demand number;
    v_monthtext varchar2(3);
    v_monthvalue number;

    i number;

    code number;

    CURSOR c_get_capacity_rollup IS select par.INVESTMENT,
    decode(par.partime,null,children.childtime,par.par time),
    decode(par.BUDGET,null,children.BUDGET,0,children. BUDGET,par.BUDGET),
    decode(par.SUPPLYSIDE,null,children.SUPPLYSIDE,0,c hildren.SUPPLYSIDE,par.SUPPLYSIDE),
    decode(par.PIPELINE,null,children.PIPELINE,0,child ren.PIPELINE,par.PIPELINE),
    decode(par.DEMANDSIDE,null,children.DEMANDSIDE,0,c hildren.DEMANDSIDE,par.DEMANDSIDE),
    case (decode(par.monthvalue,null,children.monthvalue,pa r.monthvalue))
    when 1 then 'Jan'
    when 2 then 'Feb'
    when 3 then 'Mar'
    when 4 then 'Apr'
    when 5 then 'May'
    when 6 then 'Jun'
    when 7 then 'Jul'
    when 8 then 'Aug'
    when 9 then 'Sep'
    when 10 then 'Oct'
    when 11 then 'Nov'
    when 12 then 'Dec'
    else ''
    end as monthtext,
    (decode(par.monthvalue,null,children.monthvalue,pa r.monthvalue)) monthvalue
    from
    (select
    (budp.periodyear || '-' || lpad(budp.periodnumber, 2, '0')) partime,
    reqp.name INVESTMENT,
    budp.budget BUDGET,
    budp.actual SUPPLYSIDE,
    budp.user1 PIPELINE,
    budp.user4 DEMANDSIDE,
    budp.periodnumber monthvalue
    from tr_request reqp
    inner join tr_resourcebudget budp on reqp.requestid = budp.requestid
    where budp.periodyear= p_year
    and reqp.requestid = (select I_GDC.REQUESTID
    from TR_REQUEST I_GDC
    where I_GDC.NAME = p_gdcpara
    and A7_GET_RQV (I_GDC.REQUESTID, 'Investment Category', 'C') = 'Global Delivery Centre (GDC)'
    and (p_gdc = 'ALL' or p_gdc = I_GDC.NAME)
    and exists (select null
    from TR_REQUESTACCESS RA
    where RA.REQUESTID = I_GDC.REQUESTID
    and RA.RESOURCEID = p_userid)))par
    FULL OUTER JOIN
    (select
    (budc.periodyear || '-' || lpad(budc.periodnumber, 2, '0')) childtime,
    SUM(budc.budget) BUDGET,
    SUM(budc.actual) SUPPLYSIDE,
    SUM(budc.user1) PIPELINE,
    SUM(budc.user4) DEMANDSIDE,
    budc.periodnumber monthvalue
    from tr_request reqc
    inner join tr_resourcebudget budc on reqc.requestid = budc.requestid
    where budc.periodyear= p_year
    and reqc.requestid IN (SELECT requestid FROM TR_REQUEST
    WHERE CONNECT_BY_ISLEAF=1
    CONNECT BY PRIOR requestid = parentid
    START WITH requestid = (select I_GDC.REQUESTID
    from TR_REQUEST I_GDC
    where I_GDC.NAME = p_gdcpara
    and A7_GET_RQV (I_GDC.REQUESTID, 'Investment Category', 'C') = 'Global Delivery Centre (GDC)'
    and (p_gdc = 'ALL' or p_gdc = I_GDC.NAME)
    and exists ( select null
    from TR_REQUESTACCESS RA
    where RA.REQUESTID = I_GDC.REQUESTID
    and RA.RESOURCEID = p_userid)))
    group by budc.periodnumber, (budc.periodyear || '-' || lpad(budc.periodnumber, 2, '0')))children
    ON par.partime = children.childtime;


    begin

    if not c_get_capacity_rollup%ISOPEN then
    open c_get_capacity_rollup;
    end if;

    i := 0;
    v_capacity_rollup_si := ATOS_CAPACITY_ROLLUP_TYPE_SI();

    loop
    fetch c_get_capacity_rollup into v_name, v_month, v_budget, v_supply, v_pipeline, v_demand, v_monthtext,v_monthvalue;

    exit when c_get_capacity_rollup%NOTFOUND;

    i := i + 1;
    v_capacity_rollup_si.extend(1);
    v_capacity_rollup_si(i) := ATOS_CAPACITY_ROLLUP_RECORD_SI(v_name, v_month, v_budget, v_supply, v_pipeline, v_demand, v_monthtext, v_monthvalue);

    end loop;
    close c_get_capacity_rollup;

    return v_capacity_rollup_si;

    end ATOS_GET_GDC_ROLLUP;
    /

    when i run below query :-

    select * from table(ATOS_GET_GDC_ROLLUP('India',2013,400,'India' ))

    i am getting missing paranthisis error at line 18 and 91. But when i run query for cursor then i am getting correct output.Please tell me know what is wrong in above query

  2. #2
    Join Date
    Jun 2013
    Posts
    2
    I am using below version -

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    "CORE 10.2.0.1.0 Production"
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

Posting Permissions

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