Solved

Using Rollup / Connect together in one query

Posted on 2009-04-06
8
1,615 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
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:ishando
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sethumurugan
Comment Utility
Thank you very much

Let me try it tonight and get back to you

Sethu Murugan
0
 

Author Comment

by:sethumurugan
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now