Select Sum Colmns Of A CURSOR

i have database procedure to insert data from selected query into master/detail tables
the second step ( insert into the detail table : d_table is from the following cusrosr

   
   Cursor c_dent IS
           select 
 tran_no,doc_date,acc_no,cost_center,
 sum(debit) debit,sum(credit) credit
 from(
 select
   a.acc_no,b.tr_code,b.doc_date,b.tran_no,a.cost_center
  , a.describt,a.debit,a.credit
                                                 
                     from   
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join acc_master_file c
                        on a.acc_no= c.acc_no
                        
                    WHERE
                    b.doc_date between to_date('2015-10-01','yyyy-mm-dd') and to_date('2015-10-31','yyyy-mm-dd')
                    )
                    group by tran_no,doc_date,tr_code,acc_no,cost_center;

Open in new window


then , fetching the cursor and loop all its records to insert into table  : d_trans

now the problem is in the first step which is inserting into master table (m_trans)
the records requested to insert is most likly summary of the above cursor , which its syntax is :

 Cursor c_ent IS
           select 
 tran_no,doc_date,
 from(
 select
   a.acc_no,b.tr_code,b.doc_date,b.tran_no,a.cost_center
  , a.describt,a.debit,a.credit
                                                 
                     from   
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join acc_master_file c
                        on a.acc_no= c.acc_no
                        
                    WHERE
                    b.doc_date between to_date('2015-10-01','yyyy-mm-dd') and to_date('2015-10-31','yyyy-mm-dd')
                    )
                    group by tran_no,doc_date;

Open in new window

note that the second cursor is same as the first one , but it is only summary by tran_no,doc_date , where i expect to return one record only , while the first cursor will return more than one record (because it will contain acc_no,cost_center,sum(debit) and sum(credit)
the forign key is : tran_no
now , i want instead of calling dataset 2 times in the 2 cursors , i want just to select one record ( tran_no and doc_date plus some distinct additional data from that details using case statement ) from the first cursor to insert it in the master table
how possible this may be
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
So if the first cursor gives you 2 records for a single combination of tran_no,doc_date  you want to insert only 1 record in m_trans ?
But what are the additional data ?
And what is the reason for using a cursor to insert trans_d instead of doing a direct insert?
Are there missing columns ? Columnvalue not within the cursor ?

I am thinking of the insert all statement in combination with the row_number() over
INSERT ALL
WHEN 1 = 1 THEN  -- insert all selected records in the details
  INTO D_TRANS (tran_no,doc_date,acc_no,cost_center,debit, credit)
    VALUES
                (tran_no,doc_date,acc_no,cost_center,debit, credit)
WHEN RN = 1 THEN -- insert only the first record per tran_no, doc_date in the master
  INTO M_TRANS (tran_no,doc_date, ,debit, credit, ?acc_no) VALUES (tran_no,doc_date, master_debit, master_credit, ?acc_no)  -- if there are more acc_no on the same tran_no,doc_date what to choose?)
 select
 tran_no,doc_date,acc_no,cost_center,
 sum(debit) debit,sum(credit) credit
 , ROW_NUMBER() OVER (PARTITION BY tran_no,doc_date ORDER BY tr_code,acc_no,cost_center) RN
 , SUM(debit) OVER (PARTITION BY tran_no,doc_date) master_debit                                              
 , SUM(credit) OVER (PARTITION BY tran_no,doc_date) master_credit                                              
 from(
 select
   a.acc_no,b.tr_code,b.doc_date,b.tran_no,a.cost_center
  , a.describt,a.debit,a.credit
                     from  
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join acc_master_file c
                        on a.acc_no= c.acc_no
                       
                    WHERE
                    b.doc_date between to_date('2015-10-01','yyyy-mm-dd') and to_date('2015-10-31','yyyy-mm-dd')
                    )
                    group by tran_no,doc_date,tr_code,acc_no,cost_center;
NiceMan331Author Commented:
first of all i admit that my syntax is too complex , cause of that i shortcut it in the body of my original question , because of that i requested the help to find more easier way

and here is the complete sql body for inserting into m_trans


 
select tran_no,doc_date,tr_code,c_user,int_tr_code,max(sup_name),
 max(describt),max(m_desc) from(
 select tran_no,doc_date,tr_code,c_user,int_tr_code,
 acc_no,cost_center,
 sum(debit),sum(credit),max(sup_name) sup_name,
 max(describt) describt,max(m_desc) m_desc from(
 select
   TO_NUMBER (c.code_trans) acc_no,
   (case 
   when (c.code_trans between 99400 and 99850) and (a.cost_center between 100 and 199) then '100'
     when (c.code_trans between 99400 and 99850) and (a.cost_center between 310 and 319) then '310'
      when (c.code_trans between 99400 and 99850) and (a.cost_center in(501,320,450)) then a.cost_center
      else null end) cost_center,
   TO_NUMBER (a.cost_center) c_center,a.describt,a.debit,a.credit,
b.doc_date,b.tran_no,b.head_desc,B.TR_CODE, b.c_user,
(case when b.tr_code in(4,5) then 60 else 20 end) int_tr_code
          ,st.sup_no , initcap(sf.esup_name) Sup_Name,
   (CASE
                WHEN a.acc_no=  '75340' then 'Pay Vendors '
                 WHEN a.acc_no=  '75342' then 'Pay Cash '
                  WHEN a.acc_no=  '75860' then 'Pay OvTime '
                   WHEN a.acc_no=  '76410' then 'Import Misc '
                    WHEN a.acc_no=  '77250' then 'In Maint '
                     WHEN a.acc_no=  '73310' then 'Esc Visa '
                 
                       WHEN (b.tr_code not in (3,4)) and (a.acc_no in('65320','66410','61400')) then  'Imported Items '
                       when b.tr_code in(93,48,44) then  'Sales Collecetions '
                       when c.up_acc = '55600' and a.debit > 0 then  'Cash Deposit '
                       when   b.tr_code in(60,43) and c.up_acc = '55600' then 'Release Cash ' || a.describt
                    
                       
                       end) m_desc 
                        
                         
                     from   
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join a.master_file c
                        on a.acc_no= c.acc_no
                        left join  a.sup_trans st
  on  st.TRAN_DATE=b.DOC_DATE
          AND st.TRAN_NO1= b.tran_no
          AND (st.LINE_NO=a.LINE_NO or st.line_no is null)
          left join a.sup_master sf
          on sf.sup_no = st.sup_no
                    WHERE   
                           b.doc_date >= TO_DATE ('2015-10-01', 'yyyy-mm-dd')
                           and  b.doc_date <= TO_DATE ('2015-10-31', 'yyyy-mm-dd')
                               and tran_no = v_ent
and tr_code = v_tr
                    )
                    group by tran_no,doc_date,tr_code,c_user,int_tr_code,acc_no,cost_center)
    group by tran_no,doc_date,tr_code,c_user,int_tr_code;
                       

Open in new window


1- the procedure is : get_mast_ent(v_ent number,v_tr number)
  exec get_mast_ent(tran_no,tr_code)
so it will bring only records belongs to selected tran_no and tr-code within range of date
it is expected to return one record only , if return more , it must raise

the above sql is suitable to insert into m_trans with value of all of those columns :
select tran_no,doc_date,tr_code,c_user,int_tr_code,max(sup_name),
 max(describt),max(m_desc)
and here note as i admit i make it too complex , in the sub query
(case when b.tr_code in(4,5) then 60 else 20 end) int_tr_code

Open in new window

     
 
  initcap(sf.esup_name) Sup_Name,

Open in new window

 
 (CASE
                WHEN a.acc_no=  '75340' then 'Pay Vendors '
                 WHEN a.acc_no=  '75342' then 'Pay Cash '
                  WHEN a.acc_no=  '75860' then 'Pay OvTime '
                   WHEN a.acc_no=  '76410' then 'Import Misc '
                    WHEN a.acc_no=  '77250' then 'In Maint '
                     WHEN a.acc_no=  '73310' then 'Esc Visa '
                 
                       WHEN (b.tr_code not in (3,4)) and (a.acc_no in('65320','66410','61400')) then  'Imported Items '
                       when b.tr_code in(93,48,44) then  'Sales Collecetions '
                       when c.up_acc = '55600' and a.debit > 0 then  'Cash Deposit '
                       when   b.tr_code in(60,43) and c.up_acc = '55600' then 'Release Cash ' || a.describt
                    
                       
                       end) m_desc 
                 

Open in new window

those 3 calculation  are not requested for table d_trans , but only for m_trans , i just want to fetch inside the sub_query to check value of some columns  and return top one  expressions (
it may return a values in one or more records of the sub_query , in the main query i requested top one of those expressions ,
please look for the attached excell sheet
the sheet raw data is the data of the 1st sub query , assuming the 2nd sub query is simple and no issue with it
for m_trans we will select only columns show in sheet : for d_trans
now for m_trans , look at the last 3 columns in yellow header , and note that some rows only having data , here , we can select data having max length as per shown in the other 3 highlighted by blue
in case the length is equal , as for m_desc , we will select any of them as per described in sheet : for m_trans
last thing :
the filed doc_date is not always fixed , it is changeable time to time , so i would like to include it as variable in the procedure but i will give it value in the form as global variable
sample_1.xlsx
flow01Commented:
If all necessary fields are in the first select you can use the insert all method I already mentioned, to fill both table with one statement.
You will only need to add the line with the row_number() over to your select

The length part i don't  understand yet:
If the length of those columns in m_trans is limited ,  you can decide to only use that part of the column that fits or leave it empty.
That could look like

WHEN RN = 1 THEN -- insert only the first record per tran_no, doc_date in the master
  INTO M_TRANS (Tran_no,      Doc_Date,      tr_code,      c_user,      int_tr_code,      sup_name,      describit,      m_desc)
        VALUES (Tran_no,      Doc_Date,      tr_code,      c_user,      int_tr_code,      
            substr(sup_name,1,11) /*limit method*/,      
            substr(describit,1,11) /*limit method*/,      
            case   /*take or leave it method*/
            when length(m_desc) <= 11 then m_desc
            else null
            end
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
let describe more about the last 3 columns , as per excell example , each record in the first query may return value or not depending on the value of acc no , then I will select for m trans the maximum string length from those
NiceMan331Author Commented:
i did with small adjustment to your code
i make the statement

   
INSERT ALL
    WHEN gr = 1 THEN  -- insert all selected records in the details
  INTO m_trans (fields )
     values( )
               
WHEN gr = 2 THEN
     -- insert only the first record per tran_no, doc_date in the master
 INTO d_trans (fields )
values()
WITH CC_ACCNO
     AS (
      SELECT the details query )
                    select 1 gr ,tran_no,doc_date,tr_code,****** etc
                     FROM CC_ACCNO
                     group by tran_no,doc_date,tr_code  ---- totals for m_trans
                     union all
                     select 2,tran_no,doc_date,tr_code,**** etc
                     FROM CC_ACCNO;
       
1- when i run the query only , it displayed the correct requested records with 1 value for GR for m_trans , and for 2 value for GR for d_trans
2-the procedure created successfully with no issue
3- when execute it , it sent error message raising trigger error ( trigger of d_trans to check parent record existing in m_trans )
4- to investigate , i removed insert statement of d_trans , the procedure executed successfully
5- so it required to insert m_trans first then d_trans
6- one thing more , for d_trans , it is required serial number for each record , how i can add it to the query ? temporary i solved it as adding same value from the other table
NiceMan331Author Commented:
sorry I may be wrong , I think the issue is the serial which I refer lastly , let me search tomorrow about how to correctly add it then to try again , sorry
NiceMan331Author Commented:
i adjust to use row number  for d_trans
but same error
table m_trans is mutating trigger error
which is i think looking for the parent record
flow01Commented:
Why the union :  the insert all makes it possible to insert 2 records in different tables , based on 1 selected record,
Is there a trigger on m_trans or/and d_trans ? If yes, does it also fire if you comment out the insert into  d_trans ?
NiceMan331Author Commented:
yes there is trigger on d_trans ,    when I adjust the procedure to insert only on m_ trans it success , when adjust to only insert into d_trans it fail due to no parent record , in my previous method before asking I been using 2 cursors , 1 to insert into m_trans then open the other cursor to insert into dtrans and success without commit
flow01Commented:
1) And what is the trigger doing ?
2) I don't expect it wil make a real differrenct but what happens if you change the order of the insert
(making them both active again)
NiceMan331Author Commented:
And what is the trigger doing ?
i really don't know , it is not mine , it is server side , and its script is too long

I don't expect it wil make a real differrenct but what happens if you change the order of the insert
(making them both active again)
no difference , same error
flow01Commented:
Then  go back to the cursor (use the select from the last insert all) and combine it with a bulk collect and 2 forall statements.  Then you still need only once fetch the data.
You know the technic ?  I can provide  the basics later.
NiceMan331Author Commented:
ok, I will wait your instructions
flow01Commented:
DECLARE
Cursor c_ent
....  but use the select part as in the insert all-statement (inclusive the  row_number()  line giving column rn)

type t_ent_type is table of c_ent%rowtype index by binary_integer;
t_ent    t_ent_type; -- pl/sql collection of detail records
t_ent_m  t_ent_type; -- pl/sql collection of master records
BEGIN
  OPEN C_ENT;
  FETCH C_ENT BULK COLLECT INTO T_ENT;  -- this goes into memory : if you have large number of records they can be fetched in smaller units using the LIMIT clause
  CLOSE C_ENT;

   -- insert all detailrecords
   FORALL i1 in 1  .. t_ent.count
   insert
   into d_trans (col1, col2, ..) VALUES (t_ent(i1).col1,t_ent(i1).col1);
   
   -- gather the masterrecords
   FOR i1 in 1 .. t_ent.count LOOP
     IF t_ent(i1).rn = 1 THEN
       t_ent2(t_ent2.count + 1 := t_ent(i1);
     END IF
   END LOOP;
   
   -- insert all masterrecord
   FORALL i1 in 1  .. t_ent2.count
   insert
   into m_trans (col1, col2, ..) VALUES (t_ent2(i1).col1,t_ent2(i1).col1);
   
END;
NiceMan331Author Commented:
1- i read the trigger carefullt today , in some part of it it is opening table m_trans
and updating 2 column of it based on a value in d_trans
so , i think here we have to insert the master first to not get same error

2-
into d_trans (col1, col2, ..) VALUES (t_ent(i1).col1,t_ent(i1).col1);

Open in new window


i didn't understand how to fill it ?
is it like this excatly as you type , or i should change
into d_trans (col1, col2, ..) VALUES (t_ent(i1).col1,t_ent(i2).col2); ?
flow01Commented:
The example should have had the col2, but of course you will have to change the column names in such a way that the first list of column_names matches the column_names of the table and the second list matches the column_names of your cursor that should be used to provide the values.
I used the same names in both lists because I most try to choose names in my select statement to match the names in the table.
NiceMan331Author Commented:
in line 116
after for insert

 values(curr_y, t_ent(i1).period_no ,t_ent(i1).int_tr_code

Open in new window


i got this error for it and all other fileds too

Line	Column	Error
116	24	PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
116	24	PLS-00382: expression is of wrong type

Open in new window

NiceMan331Author Commented:
in google search i found this procedure

PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      UPDATE employees emp
         SET emp.salary = emp.salary + 
             emp.salary * increase_salary.increase_pct_in
       WHERE emp.employee_id = employee_rec.employee_id;
   END LOOP;
END increase_salary;

Open in new window


can use same to insert all ? or may be will gives same error ?
NiceMan331Author Commented:
it success like this
after the select
)
loop
if  x.gr = 1 THEN  
    insert INTO  m_trans
values (***)
end if;
if  x.gr = 2 THEN  
    insert INTO  D_trans
values (***)
end if;

Open in new window


i think the problem in the first solution been < insert all
flow01Commented:
"PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND " : your probably on an oracle version 10g or early 11g : in the later 11g versions you can use this construction

The insert all was not the problem , but the insert all in combination with the trigger : that resulted in accessing the same table within the trigger as being a table that was inserted to, That causes the mutating table problem.  

De workaround is then doing the traditional  inserts in the loop : it works but is less fast then a forall insert.
However I see that you are still making the difference based ong x,gr:  so I guess you are still using the union in the select : that means that you are still executing nearly the same select twice (first part and second part of the union) and that was just the thing you where trying to avoid.    

You can still use the select in combination with the row_number to get the value of rn as we used in combination with the bulk select:
Then you have one select,  with the extra work because of the row_number evalution and you can use the loop
)
loop
if  x.rn = 1 THEN  
    insert INTO  m_trans
values (***)
end if;

 insert INTO  D_trans..

end loop;

And I think you can also still use the BULK COLLECT and do the insert based on the collection without the forall: but you will have to check that with your oracle version
Then you get the performance gain of a single select and the bulk collect.

DECLARE
Cursor c_ent
....  but use the select part as in the insert all-statement (inclusive the  row_number()  line giving column rn)

type t_ent_type is table of c_ent%rowtype index by binary_integer;
t_ent    t_ent_type; -- pl/sql collection of detail records
BEGIN
  OPEN C_ENT;
  FETCH C_ENT BULK COLLECT INTO T_ENT;  -- this goes into memory : if you have large number of records they can be fetched in smaller units using the LIMIT clause
  CLOSE C_ENT;

   -- insert all masterrecord without using the FORALL
   FOR i1 in 1  .. t_ent.count LOOP
     IF t_ent(i1).rn = 1 THEN
       insert
       into m_trans (col1, col2, ..) VALUES (t_ent2(i1).col1,t_ent2(i1).col2);
     END IF;
   END LOOP;

   -- insert all detailrecords without using the FORALL
   FOR i1 in 1  .. t_ent.count LOOP
     insert
     into d_trans (col1, col2, ..) VALUES (t_ent(i1).col1,t_ent(i1).col2);
   END LOOP;
 
   
END;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
your probably on an oracle version 10g or early 11g
exactly , i'm in 10 g

so I guess you are still using the union in the select :
yes , because i have problem in summarizing the 3 fields
select
tran_no2,doc_date,tr_code,create_alpha_user,int_tr_code,
 acc_no,cost_center,max(chk_no) chk_no,period_no,
 ROW_NUMBER() OVER (PARTITION BY tran_no2,tr_code ORDER BY tran_no2) RN,
 max(sup_name) OVER (PARTITION BY tran_no2,tr_code ORDER BY tran_no2 ) sup_name,
  max(describt) OVER (PARTITION BY tran_no2,tr_code ) describt,
   max(m_desc) OVER (PARTITION BY tran_no2,tr_code ) m_desc,
 sum(l_debit) debit,sum(l_credit) credit
 from(
*******
, a.describt,
initcap(sf.esup_name) Sup_Name
*******
)
 group by tran_no2,doc_date,period_no,tr_code,create_alpha_user,int_tr_code,acc_no,cost_center

Open in new window

here , to perform max over partition for sup_name for example , it required
this fields to be group by , where i dodn't need it as group by in down
if this one solve , i will not use again the union
NiceMan331Author Commented:
i compiled i procedure as your last post , it success also
but it consume same time of the previous last one ( before your last post )
it is ok in general , around 2 seconds for both
i think we can reduce the time more by :
1- if you find a way for my last post . so i can skip the union all
2-the expression : sup_name , coming from those tables in this statement from the 1st query as mensioned in my 1st overall post

  left join  a.sup_trans st
  on  st.TRAN_DATE=b.DOC_DATE
          AND st.TRAN_NO1= b.tran_no
          AND (st.LINE_NO=a.LINE_NO or st.line_no is null)
          left join a.sup_master sf
          on sf.sup_no = st.sup_no

Open in new window



here , only some records having data matched the condition
is it the best way to do the join as i did , or any other better way to select with less amount of time ?
NiceMan331Author Commented:
no sorry , just for test i removed that last 2 join , and try , it consumed same amount of time , 2 seconds
but still the question is : is it the best way to get an expression : sup_name ?
and now , only the 1st request
so I guess you are still using the union in the select :
flow01Commented:
Basic :  originally you did have a cursor for populating  de detailrecord that contained all columns that where  necessary for populating the masterrecord  
In between you have  changed your cursor (i  see a different group by , different column names :) that makes it difficult for me to keep track.
If your union-select worked : Take the select of the first part of your union (that  still contains the information for populating both the detailrecords and the masterrecords) and only add the
 ROW_NUMBER() OVER (PARTITION BY  ... RN  line
Does that select work ?
NiceMan331Author Commented:
no , the first select necessary records for m_ trans only , and then the union included the necessary items for d_ trans , anyhow , let start my question in different way , in the  sub query , each record return value or null for field sup_name , simple , now I want to select a value from column sup_name which having maximum length from that column , just to put it in m_trans
flow01Commented:
1)
Ok , so you switched the order of the selects (you started with the d_trans  in you first post).
So take the second part of your union and only add the
 ROW_NUMBER() OVER (PARTITION BY  ... RN  line
Does that select work ?

2)  substr(x,1,maxvalue) x    limits the length of x and you can use it  in the select or in the insert (in de values part)



3) provide an example
NiceMan331Author Commented:
i may be not more experiance in descriping the actual problem
ok , please let go to your code here
http://www.experts-exchange.com/questions/28813779/Select-Sum-Colmns-Of-A-CURSOR.html#a41194927
if you notice in between the last code
select
 tran_no,doc_date,acc_no,cost_center,
 sum(debit) debit,sum(credit) credit
 , ROW_NUMBER() OVER (PARTITION BY tran_no,doc_date ORDER BY tr_code,acc_no,cost_center) RN
 , SUM(debit) OVER (PARTITION BY tran_no,doc_date) master_debit                                              
 , SUM(credit) OVER (PARTITION BY tran_no,doc_date) master_credit                                              
 from(
 select
   a.acc_no,b.tr_code,b.doc_date,b.tran_no,a.cost_center
  , a.describt,a.debit,a.credit
                     from  
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join acc_master_file c
                        on a.acc_no= c.acc_no
                       
                    WHERE
                    b.doc_date between to_date('2015-10-01','yyyy-mm-dd') and to_date('2015-10-31','yyyy-mm-dd')
                    )
                    group by tran_no,doc_date,tr_code,acc_no,cost_center; 

Open in new window



the
 SUM(credit) OVER (PARTITION BY tran_no,doc_date)
not working here , it says , not a group by expression , referreing to : credit
if this one work , the same thing i need
 max(sup_name) OVER (PARTITION BY tran_no,doc_date)  will be ok for me
flow01Commented:
Ok,  the combination of a group by and the partition does not work:  
So  make your original d_trans selection with 'normal' max and sum functions and  access that query to determine sum and rownum on the masterlevel.  

Because I can't access your tables I created a working example based on the user_views

with sel_d_trans
as
(
select  c.table_name, column_id
,max(t.last_analyzed) supname
,sum(data_length) credit
from user_tab_columns c,
user_tables t
where t.table_name = c.table_name
group by c.table_name, column_id
)
select sel_d_trans.*
, sum(credit) over (partition by table_name) mastercredit
, row_number() over (partition by table_name order by column_id desc) rn
from sel_d_trans
order by table_name

that would make your query  

with sel_d_trans
as
(
select
 tran_no,doc_date,acc_no,cost_center,
 -- max(sup_name) supname
 sum(debit) debit,sum(credit) credit
 from(
 select
   a.acc_no,b.tr_code,b.doc_date,b.tran_no,a.cost_center
  , a.describt,a.debit,a.credit
                     from  
                      a.m_trans b
                                               LEFT JOIN a.d_trans a
                             ON     a.year = b.year
                                AND a.period_no = b.period_no
                                AND a.tr_code = b.tr_code
                                AND a.tran_no = b.tran_no
                                AND a.doc_date = b.doc_date
                        left join acc_master_file c
                        on a.acc_no= c.acc_no
                       
                    WHERE
                    b.doc_date between to_date('2015-10-01','yyyy-mm-dd') and to_date('2015-10-31','yyyy-mm-dd')
                    )
                    group by tran_no,doc_date,tr_code,acc_no,cost_center)
select sel_d_trans.*
 , ROW_NUMBER() OVER (PARTITION BY tran_no,doc_date ORDER BY tr_code,acc_no,cost_center) RN
 , SUM(debit) OVER (PARTITION BY tran_no,doc_date) master_debit                                              
 , SUM(credit) OVER (PARTITION BY tran_no,doc_date) master_credit                                              
from sel_d_trans
order by tran_no,doc_date,acc_no,cost_center

fingers crossed !
Don't worry if it does not work we step back to the basics and build  the masterdata within the procedure  based on 1 cursor.
NiceMan331Author Commented:
ok , this works well also
what i noticed is , all last 3 solutions consuming approximately same time in the form
finally I'm satisfied as they are all ok for me
and here , just i have a question :
you first asked me why using cursor and advised to use direct insert .
is cursor consuming more time ?
and , when i used my query of union , is it also consuming more time than the latest one ?
in Toad , both syntaxes consumed exactly same amount of time : 6 seconds
while in the form they consumed not more than 2 seconds for each ,
flow01Commented:
In general
direct insert is faster then fetching and insert separate
bulk collect is faster then fetching each single row apart
forall  insert from collection is faster then inserting row after row
the union containts 2 selects so in general it will take more time :
but if you execute a select twice , the second one will be much faster because with the first select oracle gets data from disk to memory and with a second select checks if it can reuse that memory : that is much faster.  Because the datablocks are in memory , a nearly identical select benifits from the same behaviour.
The great difference between Toad and forms i can't explain, if Toad fetches much more records to show then are on one page forms that could have an effect.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.