Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2011
    Posts
    37

    how to call dynamic query in procedure

    Dear friend,
    i have a problem but unable to solve,could u resolve it.

    i have a dynamic query for example--

    with a as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    ) ,
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno as gpfaccno,a.name as name,a.designation as designation,a.total as total,b.total2 as total2,(a.total+b.total2) as total3 from a
    inner join b on a.code=b.code order by a.name
    ;

    and i m calling this query in my procedure but it show errow my procedure is given below..

    create or replace procedure emp_history_rec (
    gpfaccno IN OUT NUMBER,
    name IN OUT NUMBER,
    designation IN OUT NUMBER,
    total IN OUT NUMBER,
    total2 IN OUT NUMBER,
    total3 IN OUT NUMBER


    )
    is
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    emcode varchar2(20);
    date1 date;
    x varchar2(255);
    z varchar2(255);
    --cursor
    cursor c1 is
    select distinct(code) from pms_employee_rec where INSTITUTECODE='09';
    begin
    for emp in c1
    loop
    dbms_output.put_line('vishwas' ||emp.code);

    select decode(max(emptnfrcntpstldate),'',to_date('01/01/2001','mm-dd-YYYY'),max(emptnfrcntpstldate)) into date1 from
    (select e.* from pms_employee_rec e where e.INSTITUTECODE='09' and code=emp.code and emptnfrcntpstldate<=to_date('December-2011','month-YYYY ')) sa ;
    dbms_output.put_line('vishwas' ||date1);

    with a as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    ) ,
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno as gpfaccno,a.name as name,a.designation as designation,a.total as total,b.total2 as total2,(a.total+b.total2) as total3 from a
    inner join b on a.code=b.code order by a.name ;
    end loop;
    end;
    could u help me to resolve this problem

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    I don't see anything "dynamic" in query you posted.

    What error did you get? If you mentioned it, I didn't see it in a mess you posted.

    Anyway: PL/SQL requires you to SELECT INTO, which you don't have. Also, it would help if you format code and enclose it into the [code] tags to preserve formatting and make it easier to read.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    >how to call dynamic query in procedure
    you don't " call dynamic query"; but you can call procedure which contains a query.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  4. #4
    Join Date
    Jul 2011
    Posts
    37
    Quote Originally Posted by anacedent View Post
    >how to call dynamic query in procedure
    you don't " call dynamic query"; but you can call procedure which contains a query.
    hi,
    anacedent,

    actually i will never call such type query in my procedure.it will working fine and giving me result but when i call it with in my procedure i m unable to customize it.according to u i have all-ready use into parameter but i m unable to fit this query in my procedure. how i fit this query in my procedure? can u use some code to explain it.my code is given below..

    with a as
    (SELECT pm.gpfaccno into gpfaccno_query,pm.code into code_query,pm.name itno name_query,pm.designation into designation_query,SUM(pe.VALUE) into total_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    ),
    b as
    (SELECT pm.gpfaccno into gpfaccno_query,pm.code into code_query,pm.name into name_query,pm.designation into designation_query,SUM(pe.VALUE) into total2_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno as gpfaccno,a.name as name,a.designation as designation,a.total as total,b.total2 as total2,(a.total+b.total2) as total3 from a
    inner join b on a.code=b.code order by a.name ;


    and also let me know how to store these query result in ref cursor.
    thanks
    Last edited by vishwas; 12-13-12 at 23:38.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    select emp_id, hire_date, deptno into v_empid, v_hiredate, v_deptno from emp;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  6. #6
    Join Date
    Jul 2011
    Posts
    37
    hi
    ya i m all-ready use this type query according to u.but when i compile it.the msg is
    "end of file or communication"
    "not connected to oracle"
    yet i m all-ready connected to oracle.
    my code is given below--

    create or replace procedure emp_history_rec (
    gpfaccno IN OUT NUMBER,
    name IN OUT NUMBER,
    designation IN OUT NUMBER,
    total IN OUT NUMBER,
    total2 IN OUT NUMBER,
    total3 IN OUT NUMBER)
    is
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    emcode varchar2(20);
    date1 date;
    x varchar2(255);
    z varchar2(255);
    gpfaccno_query varchar2(20);
    code_query varchar2(20);
    name_query varchar2(20);
    designation_query varchar2(20);
    total_query number;
    total2_query number;
    --cursor
    cursor c1 is
    select distinct(code) from pms_employee_rec where INSTITUTECODE='09';
    begin
    for emp in c1
    loop
    --dbms_output.put_line('vishwas' ||emp.code);

    select decode(max(emptnfrcntpstldate),'',to_date('01/01/2001','mm-dd-YYYY'),max(emptnfrcntpstldate)) into date1 from
    (select e.* from pms_employee_rec e where e.INSTITUTECODE='09' and code=emp.code and emptnfrcntpstldate<=to_date('December-2011','month-YYYY ')) sa ;
    --dbms_output.put_line('vishwas' ||date1);

    with a as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total into gpfaccno_query,code_query,name_query,designation_q uery,total_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    ),
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2 into gpfaccno_query,code_query,name_query,designation_q uery,total2_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno as gpfaccno,a.name as name,a.designation as designation,a.total as total,b.total2 as total2,(a.total+b.total2) as total3 from a
    inner join b on a.code=b.code order by a.name ;
    end loop;
    end;

  7. #7
    Join Date
    Jul 2011
    Posts
    37
    soory,
    i got my mistake,
    thanks for ur help

  8. #8
    Join Date
    Jul 2011
    Posts
    37
    this procedure unable to compile,and break the connection
    problem is not solve yet
    code is given below..
    create or replace procedure emp_history_rec (
    gpfaccno IN OUT NUMBER,
    name IN OUT NUMBER,
    designation IN OUT NUMBER,
    total IN OUT NUMBER,
    total2 IN OUT NUMBER,
    total3 IN OUT NUMBER)
    is
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    emcode varchar2(20);
    date1 date;
    x varchar2(255);
    z varchar2(255);
    gpfaccno_query varchar2(20);
    code_query varchar2(20);
    name_query varchar2(20);
    designation_query varchar2(20);
    total_query number;
    total2_query number;
    --cursor
    cursor c1 is
    select distinct(code) from pms_employee_rec where INSTITUTECODE='09';
    begin
    for emp in c1
    loop
    --dbms_output.put_line('vishwas' ||emp.code);

    select decode(max(emptnfrcntpstldate),'',to_date('01/01/2001','mm-dd-YYYY'),max(emptnfrcntpstldate)) into date1 from
    (select e.* from pms_employee_rec e where e.INSTITUTECODE='09' and code=emp.code and emptnfrcntpstldate<=to_date('December-2011','month-YYYY ')) sa ;
    --dbms_output.put_line('vishwas' ||date1);

    with a as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total into gpfaccno_query,code_query,name_query,designation_q uery,total_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    ),
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2 into gpfaccno_query,code_query,name_query,designation_q uery,total2_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and emptnfrcntpstldate=date1)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno,a.name,a.designation,a.total,b.total2,( a.total+b.total2) into gpfaccno,name,designation,total,total2,total3
    from a
    inner join b on a.code=b.code order by a.name ;
    end loop;
    end;

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    please realize & understand that without ALL your tables we can not run, test or improve posted SQL.

    >this procedure unable to compile,
    how can we reproduce what you report?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  10. #10
    Join Date
    Feb 2005
    Posts
    56
    Your don't need INTO in your nested selects, just in the outer select!!

  11. #11
    Join Date
    Jul 2011
    Posts
    37
    hi,
    outrider,
    can u tell me where i left into parameter in my query actually i m unable to find..

    my code is given below..

    create or replace procedure emp_history_rec
    (
    gpfaccno OUT varchar2,
    name OUT Varchar2,
    designation OUT varchar2,
    total OUT NUMBER,
    total2 OUT NUMBER,
    total3 OUT NUMBER)
    is
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    emcode varchar2(20);
    date1 date;
    --for query one
    date2 varchar2(20);
    gpfaccno_query varchar2(20);
    code_query varchar2(20);
    name_query varchar2(20);
    designation_query varchar2(20);
    total_query number;
    --for query second
    gpfaccno_query1 varchar2(20);
    code_query1 varchar2(20);
    name_query1 varchar2(20);
    designation_query1 varchar2(20);
    total_query1 number;


    g_account varchar2(20);
    c_code varchar2(20);
    n_name varchar2(20);
    d_designation varchar2(20);
    t_total number;
    t_total2 number;
    t_total3 number;
    --cursor
    cursor c1 is
    select distinct(code) from pms_employee_rec where INSTITUTECODE='09';
    begin
    for emp in c1
    loop
    --dbms_output.put_line('vishwas' ||emp.code);

    select decode(max(emptnfrcntpstldate),'',to_date('01/01/2001','mm-dd-YYYY'),max(emptnfrcntpstldate)) into date1 from
    (select e.* from pms_employee_rec e where e.INSTITUTECODE='09' and code=emp.code and emptnfrcntpstldate<=to_date('December-2011','month-YYYY ')) sa ;
    --dbms_output.put_line('vishwas' ||to_char(date1,'mm/dd/YYYY')||':'||emp.code);
    date2:=to_char(date1,'mm/dd/YYYY');
    dbms_output.put_line('vishwas3' ||date2 ||':'||emp.code);

    BEGIN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := 0;

    with a as
    (
    SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total into gpfaccno_query,code_query,name_query,designation_q uery,total_query
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (5) AND pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and to_char(emptnfrcntpstldate,'mm/dd/YYYY')=date2 and pi.code=emp.code) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    )
    )
    ,
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2 into gpfaccno_query1,code_query1,name_query1,designatio n_query1,total_query1
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID IN (78) AND (pe.EMPID IN (SELECT pi.id from pms_employee_rec pi
    where pi.INSTITUTECODE='09' and to_char(emptnfrcntpstldate,'mm/dd/YYYY')=date2 and pi.code=emp.code)) AND PAYROLLID IN (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select a.gpfaccno,a.name,a.designation,a.total,b.total2,( a.total+b.total2) into g_account,n_name,d_designation,t_total,t_total2,t_ total3
    from a
    inner join b on a.code=b.code order by a.name ;

    StoO_rowcnt := SQL%ROWCOUNT;

    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    StoO_rowcnt := 2;
    WHEN NO_DATA_FOUND THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    WHEN OTHERS THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
    END;

    --dbms_output.put_line('vishwas3' ||date2);

    end loop;

    --if code_query= code_query1 then
    -- end if;
    end;

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  13. #13
    Join Date
    Feb 2005
    Posts
    56
    Code:
    with a as
    (
    SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total into 
    Code:
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2 into 
    INTO not required in these cases.

  14. #14
    Join Date
    Jul 2011
    Posts
    37
    thanks to all of my friend to solve this problem.
    but there is one more problem this procedure is taking so much time. and i m unable to optimized it.because with clause query is running in loop so it check for all emp(i.e 1000 or more).so this procedure is taking so much time..
    there is some other way to optimize query or procedure.
    my code is given below..

    create or replace procedure emp_history_rec
    (gpfaccno out varchar2,
    name out varchar2,
    designation out varchar2,
    total out number,
    total2 out number,
    total3 out number
    )
    is
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_errmsg VARCHAR2(255);
    date1 date;

    date2 varchar2(20);

    --cursor

    cursor c1 is
    select distinct(code) from pms_employee_rec where INSTITUTECODE='02';
    begin
    for emp in c1
    loop


    select decode(max(emptnfrcntpstldate),'',to_date('01/01/2001','mm-dd-YYYY'),max(emptnfrcntpstldate)) into date1 from
    (select e.* from pms_employee_rec e where e.INSTITUTECODE='02' and code=emp.code and emptnfrcntpstldate<=to_date('December-2011','month-YYYY ')) sa ;

    date2:=to_char(date1,'mm/dd/YYYY');
    --dbms_output.put_line('vishwas3' ||date2);

    BEGIN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := 0;

    with a as
    (
    SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID = 5 AND pe.EMPID = (SELECT pm.id from pms_employee_rec pm
    where pm.code=emp.code and (pm.emptnfrcntpstldate=date1
    or
    pm.emptnfrcntpstldate is null)

    ) AND pe.PAYROLLID in (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) and (pm.emptnfrcntpstldate=date1 or
    pm.emptnfrcntpstldate is null)
    group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    ,
    b as
    (SELECT pm.gpfaccno,pm.code,pm.name,pm.designation,SUM(pe. VALUE) as total2
    FROM PMS_EMP_SALITEM_DET pe join pms_employee_rec pm on pe.EMPID=pm.id
    WHERE pe.SALARYITEMID = 78 AND pe.EMPID = (SELECT pm.id from pms_employee_rec pm
    where pm.code=emp.code and (pm.emptnfrcntpstldate=date1 or
    pm.emptnfrcntpstldate is null)

    ) AND pe.PAYROLLID in (SELECT pp.ID FROM PMS_PAYROLL pp
    WHERE (pp.YEAR=(to_char(to_date('December-2011','mm-YYYY'),'YYYY')) and
    (pp.month=(to_char(to_date('December-2011','mm-YYYY'),'mm'))))

    ) and (pm.emptnfrcntpstldate=date1 or
    pm.emptnfrcntpstldate is null)
    group by pm.name,pm.gpfaccno,pm.designation,pe.empid,pm.cod e
    )
    select nvl(a.gpfaccno,'N/A'),a.name,a.designation,a.total,b.total2,(a.total +b.total2) into gpfaccno,name,designation,total,total2,total3
    from a
    inner join b on a.code=b.code order by a.name ;

    StoO_rowcnt := SQL%ROWCOUNT;
    dbms_output.put_line('vishwas3' ||gpfaccno||':'||name||':'||designation||':'||tota l||':'||total2);

    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    StoO_rowcnt := 2;
    WHEN NO_DATA_FOUND THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    WHEN OTHERS THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
    END;

    --dbms_output.put_line('vishwas3' ||tblresult.gpfaccno);

    end loop;

    --if code_query= code_query1 then
    -- end if;
    end;

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    Since NOBODY can optimize SQL just by looking at it, we need a few more details.
    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

Posting Permissions

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