Solved

Using Rollup / Connect together in one query

Posted on 2009-04-06
8
1,656 Views
Last Modified: 2013-12-18
Hi Friends,

I have a out put as you found in in Sheet Raw Data of the attached EXCEL file.  I would like to have an out put results as I found in output sheet.  I wanted to have it in single SQL.  I tried using  procedures with loops and also using connect by and rollup in EXCEL.  I could not get the results.  Can some one help me.

To give more details about the issue, Iam trying to print a General ledger of a group of accounts.  I have completed a GL without grouping.  for the given account the system will print Opening balances, for the month transaction and Closing balance.  

But now the client want to take a GL for entire group.  In my solution, the client will be able to print one report for every accounts available in a groups.

Sethu Murugan
General-Ledger-Sample.xls
0
Comment
Question by:sethumurugan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:ishando
ID: 24076504
Here is one possible approach to get the required output, though the ordering does depend on the codes being the same length, if not there could be discrepancies in the ordering.



Sample data:
ACCNT_CD   PARNT_CD        DEBIT     CREDIT DESCRIPT
---------- ---------- ---------- ---------- --------------------------------------------------
A0001                                   100 Liability - Opening Balance
A0002      A0001                        200 Current Liability Opening Balance
A0003      A0002                        300 Creditors
A0004      A0003                        400 ABC
A0005      A0003             700            XYZ
TRAN1      A0004             500            Transaction 1
TRAN2      A0004             600            Transaction 2
TRAN3      A0005             800            Transaction 3
TRAN4      A0005             900            Transaction 4
 
 
Script:
with hier as 
   (select level lvl, accnt_cd, debit, credit, sys_connect_by_path(accnt_cd, '|') path
    from gl
    connect by parnt_cd = prior accnt_cd
    start with parnt_cd is null),
 grp as
   (select accnt_cd, debit, credit, lvl, max(lvl) over (partition by 1) xlvl, 
           cast(path as varchar2(50)) path
    from hier
    union
    select a.accnt_cd, sum(b.debit), sum(b.credit), - a.lvl, max(a.lvl) over (partition by 1) xlvl, 
           cast(a.path as varchar2(50)) path
    from hier a
      inner join hier b on instr(b.path,a.path) = 1
    group by a.accnt_cd, a.lvl, a.path)
select accnt_cd, debit, credit, lvl, xlvl, path
from grp 
where lvl != -xlvl
order by case when lvl < 0 then rpad(path,6*xlvl,'|XXXXX') else path end,
         case when lvl < 0 then xlvl + lvl else lvl end;
 
Output:
ACCNT_CD        DEBIT     CREDIT        LVL       XLVL PATH
---------- ---------- ---------- ---------- ---------- ----------------------------------
A0001                        100          1          5 |A0001
A0002                        200          2          5 |A0001|A0002
A0003                        300          3          5 |A0001|A0002|A0003
A0004                        400          4          5 |A0001|A0002|A0003|A0004
TRAN1             500                     5          5 |A0001|A0002|A0003|A0004|TRAN1
TRAN2             600                     5          5 |A0001|A0002|A0003|A0004|TRAN2
A0004            1100        400         -4          5 |A0001|A0002|A0003|A0004
A0005             700                     4          5 |A0001|A0002|A0003|A0005
TRAN3             800                     5          5 |A0001|A0002|A0003|A0005|TRAN3
TRAN4             900                     5          5 |A0001|A0002|A0003|A0005|TRAN4
A0005            2400                    -4          5 |A0001|A0002|A0003|A0005
A0003            3500        700         -3          5 |A0001|A0002|A0003
A0002            3500        900         -2          5 |A0001|A0002
A0001            3500       1000         -1          5 |A0001

Open in new window

0
 

Author Comment

by:sethumurugan
ID: 24085123
Hi,

Thank you very much for that.  Its just a new concept for me.  First time am seeing such kind of sql scripting.  Hope I should be able to make it suitable for me.

I have only one small clarification on this.  Actually in the sample sheet i have mentioned few columns from my original data.  apart from the data like account code, db amt, cr_amt, there are some more columns like voucher no, cheque no etc which may not be part of grouping.  

In your script GRP part has some groping in both the select.  How can we handle with those columns.

Thank you very much.

Sethu Murugan
0
 
LVL 15

Accepted Solution

by:
ishando earned 500 total points
ID: 24085389
Probably the easiest way to handle these additional columns, would be to do a join in the outer query to get these

Something like


with hier as
   (...),
 grp as
   (...)
select g.accnt_cd, g.debit, g.credit, g.lvl, g.xlvl, g.path, gl.othercol
from grp g
  inner join gl on gl.accnt_cd = g.accnt_cd [and g.lvl > 0]  (only display on record line, not rollup line)
where lvl != -xlvl
order by case when lvl < 0 then rpad(path,6*xlvl,'|XXXXX') else path end,
         case when lvl < 0 then xlvl + lvl else lvl end;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sethumurugan
ID: 24086008
Thank you very much

Let me try it tonight and get back to you

Sethu Murugan
0
 

Author Comment

by:sethumurugan
ID: 24135662
Hi,

I have done it as per your guidence.  Actually this is a new concept which i had never tried before.  pleaase find the code i had written.  I would like to thank you for the help you have done.  Please find the final query i had written based on your sample.

I tried to acheive it using procedure.  it took hell lot of time and that too producing the results in wrong way.  this is very faster and fantastic.  

I had used various combinations in this like dynamic view.  

And finally I had achieved what i wanted.

There are just two clarification i require.  if possible you please tell me other wise you can ignore it.  without that also i will be able to handle it.

Clarification 1.

i have function which connects to the balance table and returns the opening balance for an account.  i have return a query like

select acct_code,
           decode(sign(func_get_bal(acct_code, period) ,1,decode(sign(func_get_bal(acct_code, period) ,0) db_amt,
           decode(sign(func_get_bal(acct_code, period) ,-1,decode(sign(func_get_bal(acct_code, period) ,0) cr_amt,
 from table...

in the above query it looks like the function has to be called 4 times for every record.  I think that should be a wrong way of doing it.

Can you please tell me how to do it in simple way.

Calrification 2

in your query GRP the bottom query is used for suming up the values.  there i have added a sting as 'CL balance'.  But I want that string to be 'GRAND TOTAL' if it is the total of transactions.  in other words when the level is bottom most [ 5 in 1 to 5 ], it should have differnt text.  Please find the attached sample work sheet.  red colored row should have different text.

Sethu Murugan

Sethu Murugan

insert 
        into general_ledger
        with hier 
          as 
           ( 
             select PRINT_ORDER, 
                    GL_ACCOUNT_CODE, 
                    DB_AMOUNT, 
                    CR_AMOUNT,
                    link_reference, 
                    sys_connect_by_path(GL_ACCOUNT_CODE, '|') path
               from VIEW_GENERAL_LEDGER
            connect 
                 by S_PARENT_ACCOUNT_CODE = prior GL_ACCOUNT_CODE
              start 
               with S_PARENT_ACCOUNT_CODE is null
           ),
             grp 
          as
           (
             select GL_ACCOUNT_CODE, 
                    DB_AMOUNT, 
                    CR_AMOUNT,
                    link_reference,  
                    PRINT_ORDER, 
                    max(PRINT_ORDER) over (partition by 1) xlvl, 
                    cast(path as varchar2(500)) path
               from hier
              union all
             select a.GL_ACCOUNT_CODE, 
                    sum(b.DB_AMOUNT), 
                    sum(b.CR_AMOUNT),
                    'Cl. Balance' link_reference, 
                    - a.PRINT_ORDER, 
                    max(a.PRINT_ORDER) over (partition by 1) xlvl, 
                    cast(a.path as varchar2(500)) path
               from hier a
              inner 
               join hier b on instr(b.path,a.path) = 1
              group 
                 by a.GL_ACCOUNT_CODE, a.PRINT_ORDER, a.path
           )
        select y_session_id,
               g.GL_ACCOUNT_code,
               v.S_PERIOD,
               v.S_YEAR_ID,
               v.DT_VOUCHER_DATE,
               v.S_NARRATION,
              (case 
                    when g.link_reference in ('Op. Balance','Cl. Balance') then
                         g.link_reference
                    else
                         v.S_VOUCHER_NO
                    end) S_VOUCHER_NO,
               v.VOUCHER_TYPE,
               v.N_LINE_NO,
               v.S_ACCOUNT_CODE,
               g.DB_AMOUNT,
               g.CR_AMOUNT,
               v.S_CHEQUE_BOOK_NO,
               v.S_NARATION_DETAIL,
               v.S_ENTRY_CLASS,
               v.S_CHEQUE_no,
               v.DT_CHEQUE_DATE,
               v.N_AMT_KNOCKED_OFF,
               v.S_CURRENCY_CODE,
               v.N_EXCHANGE_RATE,
               v.N_AMOUNT_IN_FOREIGN_CURRENCY,
               v.S_HEAD_OF_ACCOUNT,
               0 BALANCE,
               v.FR_EX_DB_AMOUNT,
               v.FR_EX_CR_AMOUNT,
               v.FR_EX_CURR,
               v.opp_ACC_DETAILS,
               g.print_order,
               g.xlvl,
               g.path
          from grp G,
               view_general_ledger v
         where G.PRINT_ORDER != -G.xlvl
           and g.gl_account_code =  v.gl_account_code (+)
           and g.link_reference  =  v.link_reference (+)
         order 
            by case when G.PRINT_ORDER < 0 then rpad(G.path,12 * G.xlvl,'|XXXXXXXXXXXX') else G.path end,
               case when G.PRINT_ORDER < 0 then G.xlvl + G.PRINT_ORDER else G.PRINT_ORDER end  

Open in new window

General-Ledger-Sample.xls
0
 
LVL 15

Expert Comment

by:ishando
ID: 24146342
Not sure I fully understand what you are trying to do with the function - your raw data suggests that you already the opening balance (based on description).
However, as you said is should be possible to do without the multiple calls to the function. One possibility would be to put the call in one of the sub-queries, and then the decode at a higher level.

to get the additional total for the transactions, you could try something like the attached code.
I tried with a union to GRP but this threw an error, not sure why, so went with adding TOT to the WITH clause and unioning to this. I needed to do the sum(b.credit)-nvl(a.credit,0) in order to only count the transactions and keep XLVL the same as in GRP.... I guess I could also have done max(lvl) over (partition by 1) +1 as xlvl. (added alternate using this to bottom of script)


HTH

col parent format a10
col descript format a50
 
with hier as 
   (select level lvl, accnt_cd, debit, credit, sys_connect_by_path(accnt_cd, '|') as path, descript, parnt_cd
    from gl
    connect by parnt_cd = prior accnt_cd
    start with parnt_cd is null),
 grp as
   (select accnt_cd, debit, credit, lvl, max(lvl) over (partition by 1) xlvl, 
           cast(path as varchar2(50)) path, descript, parnt_cd
    from hier
    union
    select a.accnt_cd, sum(b.debit), sum(b.credit), - a.lvl, max(a.lvl) over (partition by 1) xlvl, 
           cast(a.path as varchar2(50)) path, a.descript, a.parnt_cd
    from hier a
      inner join hier b on instr(b.path,a.path) = 1
    group by a.accnt_cd, a.lvl, a.path, a.descript, a.parnt_cd),
 tot as 
   (select accnt_cd, debit, credit, lvl, xlvl, path, descript, parnt_cd
    from (select a.accnt_cd, sum(b.debit)-nvl(a.debit,0) debit, sum(b.credit)-nvl(a.credit,0) credit, - a.lvl lvl, max(a.lvl) over (partition by 1) xlvl, 
                 cast(a.path as varchar2(50)) path, a.descript, a.parnt_cd
          from hier a
            inner join hier b on instr(b.path,a.path) = 1
          group by a.accnt_cd, a.lvl, a.path, a.descript, a.parnt_cd, a.credit, a.debit)
    where lvl = 1 - xlvl)
select accnt_cd, descript, debit, credit, parent
from (select accnt_cd, 
             rpad(' ', abs(lvl), ' ') || case when lvl = 1-xlvl then 'Closing balance for ' || descript else replace(descript,'Opening','Closing') end descript, 
             case when lvl < 0 then case when debit > credit then debit - credit end else debit end debit, 
             case when lvl < 0 then case when credit > debit then credit - debit end else credit end credit, 
             case when lvl > 0 then parnt_cd end as parent,
             case when lvl < 0 then rpad(path,6*xlvl,'|XXXXX') else path end ord1,
             case when lvl < 0 then xlvl + lvl else lvl end ord2
      from grp 
      where lvl != -xlvl
      union
      select accnt_cd, 
             rpad(' ', abs(lvl), ' ') || 'Total for ' || descript as descript, 
             case when debit > credit then debit - credit end debit, 
             case when credit > debit then credit - debit end credit, 
             null as parent,
             rpad(path,6*xlvl,'|VVVVV') ord1,
             xlvl + lvl  ord2
      from tot 
      )
order by ord1, ord2;
 
 
------ alternate TOT definition ------
 tot as 
   (select accnt_cd, debit, credit, lvl, xlvl, path, descript, parnt_cd
    from (select a.accnt_cd, sum(b.debit) debit, sum(b.credit) credit, - a.lvl lvl, max(a.lvl) over (partition by 1) + 1 as xlvl, 
                 cast(a.path as varchar2(50)) path, a.descript, a.parnt_cd
          from hier a
            inner join hier b on instr(b.path,a.path) = 1 and b.lvl > a.lvl
          group by a.accnt_cd, a.lvl, a.path, a.descript, a.parnt_cd)

Open in new window

0
 
LVL 15

Expert Comment

by:ishando
ID: 24146552
for the alternate TOT definition, need to add a few more nvls to prevent balnk values showing. Also a bug in the setting of the description:



...
from (select accnt_cd, 
             rpad(' ', abs(lvl), ' ') || case when lvl > 0 then descript
                else case when instr(descript,'Opening') > 0 then replace(descript,'Opening','Closing') 
                   else 'Closing balance for ' || descript end end descript, 
             case when lvl < 0 then case when nvl(debit,0) > nvl(credit,0) then debit - nvl(credit,0) end else debit end debit, 
             case when lvl < 0 then case when nvl(credit,0) > nvl(debit,0) then credit - nvl(debit,0) end else credit end credit, 
             case when lvl > 0 then parnt_cd end as parent,
             case when lvl < 0 then rpad(path,6*xlvl,'|XXXXX') else path end ord1,
             case when lvl < 0 then xlvl + lvl else lvl end ord2
      from grp 
      where lvl != -xlvl
      union
      select accnt_cd, 
             rpad(' ', abs(lvl), ' ') || 'Total for ' || descript as descript, 
             case when nvl(debit,0) > nvl(credit,0) then debit - nvl(credit,0) end debit, 
             case when nvl(credit,0) > nvl(debit,0) then credit - nvl(debit,0) end credit, 
             null as parent,
             rpad(path,6*xlvl,'|VVVVV') ord1,
             xlvl + lvl  ord2
      from tot 
      )
...
 
 
 
final output for me is now:
 
ACCNT_CD   DESCRIPT                                                DEBIT     CREDIT PARENT
---------- -------------------------------------------------- ---------- ---------- ----------
A0001       Liability - Opening Balance                                         100
A0002        Current Liability Opening Balance                                  200 A0001
A0003         Creditors                                                         300 A0002
A0004          ABC                                                              400 A0003
TRAN1           Transaction 1                                        500            A0004
TRAN2           Transaction 2                                        600            A0004
A0004          Total for ABC                                        1100
A0004          Closing balance for ABC                               700
A0005          XYZ                                                   700            A0003
TRAN3           Transaction 3                                        800            A0005
TRAN4           Transaction 4                                                   900 A0005
A0005          Total for XYZ                                                    100
A0005          Closing balance for XYZ                               600
A0003         Closing balance for Creditors                         1000
A0002        Current Liability Closing Balance                       800
A0001       Liability - Closing Balance                              700

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 78
make null the repeated levels 2 37
capture vmstat info and insert it into an oracle table 31 58
Excess Redo 3 32
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question