SQL Rollup resolve the balance of the responsible person's balance

The issue is Zhou Cong asked me before the project transactions, not good on the NC one-time query to. And then I got a long project reconciliation, and I released the NC node.

Now I do the general ledger, every time the leader asked me how much money the project, how much to pay, I also want to through the balance of Accounts table, but the tragedy is that the project construction must eliminate the month-end ratio of the provisional estimate, to 13 call,

She also said that NC has no way of querying a primary subject and then removing an end level.

After the query results, I want to do a summary of the first-class subjects, the effect of the implementation

SQL is nested outside of a layer, because it is found that although the first layer has wm_concat, but using rollup is useless, and later found that using rollup appears to have to aggregate the numeric columns sum

 SelectValuecode, Valuename,explanation,km,dispname,sum(debit),sum(Credit), Yearv, PERIODV, no from ( SelectWmsys.wm_concat (Gl_freevalue.valuecode) Valuecode, Wmsys.wm_concat (gl_freevalue.valuename) ValueName, Gl_detail.explanation, substr (Bd_accsubj.dispname,1,4) km, Bd_accsubj.dispname, Gl_detail.debitamount Debit, Gl_detail.creditamount credit, Gl_detail.ye ARV, GL_DETAIL.PERIODV, gl_voucher.no fromBD_ACCSUBJJoinGl_detail onGl_detail.pk_accsubj=BD_ACCSUBJ.PK_ACCSUBJJoinBd_glorgbook onBd_glorgbook.pk_glorgbook=Bd_accsubj.pk_glorgbookJoinGl_voucher onGl_detail.pk_voucher=Gl_voucher.pk_voucher Left JoinGl_freevalue onGl_detail.assid=Gl_freevalue.freevalueidwhereGl_detail.dr= '0'    andGl_detail.yearv>= the     andGl_detail.pk_systemv!='TR'--Remove closing documents at the end of the year     andGl_detail.periodv<>'xx' --Remove the beginning of the period andBd_accsubj.subjcode not inch('41040107','41040109','41040207','41040209','41040307','41040308','560106')--minus the month-end ratio and taxes.    andBd_glorgbook.glorgbookcode= '015201-0001'--Company Conditions    andGl_freevalue.valuecode like '015211%' --Project Responsibility Person conditionGroup  bygl_detail.explanation, Bd_accsubj.dispname, Gl_detail.yearv, GL_DETAIL.PERIODV, Gl_voucher.no, Gl_detail.creditamount, Gl_detail.debitamount)Group  byValuecode, ValueName, KM, Rollup ((explanation,dispname,yearv,periodv,no))Order  byValuecode,dispname, Yearv, PERIODV, no

Last night to think of a problem again, can not consider wm_concat, because where conditions of the Valuecode directly limited the project without a "department file"

As a result, SQL can

SelectGl_freevalue.valuecode, Gl_freevalue.valuename, Gl_detail.explanation, substr (bd_accsubj.disp Name,1,4) km, NVL (Bd_accsubj.dispname,' Subtotal') Subjects,--Gl_detail.debitamount,       --Gl_detail.creditamount,      sum(Gl_detail.debitamount) expenditure,sum(Gl_detail.creditamount) revenue, Gl_detail.yearv||'-'||GL_DETAIL.PERIODV period, gl_voucher.no fromBD_ACCSUBJJoinGl_detail onGl_detail.pk_accsubj=BD_ACCSUBJ.PK_ACCSUBJJoinBd_glorgbook onBd_glorgbook.pk_glorgbook=Bd_accsubj.pk_glorgbookJoinGl_voucher onGl_detail.pk_voucher=Gl_voucher.pk_voucher Left JoinGl_freevalue onGl_detail.assid=Gl_freevalue.freevalueidwhereGl_detail.dr= '0'    andGl_detail.yearv= the     andGl_detail.pk_systemv!='TR'--Remove closing documents at the end of the year     andGl_detail.periodv<>'xx' --Remove the beginning of the period andBd_accsubj.subjcode not inch('41040107','41040109','41040207','41040209','41040307','41040308','560106')--minus the month-end ratio and taxes.    andBd_glorgbook.glorgbookcode= '015201-0001'--Company Conditions    andGl_freevalue.valuecode like '015211%' --Project Responsibility Person condition Group  bySUBSTR (Bd_accsubj.dispname,1,4), rollup (Gl_freevalue.valuecode, Gl_freevalue.valuename, Gl_detail.explanation, Bd_accsubj.dispname, Gl_detail.yearv, GL_DETAIL.PERIODV, gl_voucher.no) )        Order  bySUBSTR (Bd_accsubj.dispname,1,4), Gl_freevalue.valuecode,yearv, PERIODV, no

With this SQL, rollup has a further understanding of the group by rollup in front of groups by what the sum, like here to take the top four first-level ledger account 2123 4104, and then the normal use of the group must write things.

Before the subtotal is the last, the final discovery or order by, sort the first thing that needs to be a group by behind to let the small scoring group in the back instead of all running in the last side

Example of rollup continuation of a subgroup subtotal