Home » SQL & PL/SQL » SQL & PL/SQL » Need Sql Query (10g)
Need Sql Query [message #570734] Thu, 15 November 2012 01:48 Go to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

Dear All , I have following view.
CREATE OR REPLACE FORCE VIEW ABC_V
(
   ACC_ID,
   ACC_COD,
   RECORD_LEVEL,
   PARENT_ACC_ID,
   DEBIT
)
AS
   SELECT   1,
            '2',
            1,
            0,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   2,
            '2.01',
            2,
            1,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   3,
            '2.01.01',
            3,
            2,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   4,
            '2.01.01.0001',
            4,
            3,
            500
     FROM   DUAL
   UNION ALL
   SELECT   5,
            '2.02',
            2,
            1,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   6,
            '2.02.01',
            3,
            5,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   7,
            '2.02.01.0001',
            4,
            6,
            100
     FROM   DUAL
   UNION ALL
   SELECT   8,
            '2.02.01.0002',
            4,
            6,
            600
     FROM   DUAL
   UNION ALL
   SELECT   9,
            '2.02.02',
            3,
            5,
            NULL
     FROM   DUAL
   UNION ALL
   SELECT   10,
            '2.02.02.0001',
            4,
            9,
            200
     FROM   DUAL
   UNION ALL
   SELECT   11,
            '2.02.02.0002',
            4,
            9,
            100
     FROM   DUAL
   UNION ALL
   SELECT   12,
            '2.02.02.0003',
            4,
            9,
            400
     FROM   DUAL;


Run Query
select * from mabrar.abc_v

Result Is
ACC_ID	ACC_COD	RECORD_LEVEL	PARENT_ACC_ID	DEBIT

1	2	1	0	
2	2.01	2	1	
3	2.01.01	3	2	
4	2.01.01.0001	4	3	500
5	2.02	2	1	
6	2.02.01	3	5	
7	2.02.01.0001	4	6	100
8	2.02.01.0002	4	6	600
9	2.02.02	3	5	
10	2.02.02.0001	4	9	200
11	2.02.02.0002	4	9	100
12	2.02.02.0003	4	9	400

I need a Query which show,
Value Of Debit Column where Acc_Id = 9 Must Be Sum Of debit where Acc_id in(10,11,12) Because these rows have parent_Acc_id 9
similary Debit valud of Acc_id 6 Must the Sum of debit where parent_acc_id is 6.
similary Debit valud of Acc_id 5 Must the Sum of debit where parent_acc_id is 5.
Value For Acc_Id 9 Will Be 700
Value For Acc_Id 6 Will Be 700
Value For Acc_Id 5 Will Be 1600 and so on.

how can we do it in sql query.

thanks

Asif
Re: Need Sql Query [message #570735 is a reply to message #570734] Thu, 15 November 2012 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The columns are not aligned and are difficult to read
2/ What should be the final result for the data you gave? Explain each line to the result.

Regards
Michel
Re: Need Sql Query [message #570736 is a reply to message #570734] Thu, 15 November 2012 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe:
SQL> select acc_id, acc_cod, record_level, parent_acc_id,
  2         nvl(debit,
  3             (select sum(debit) from ABC_V c where c.parent_acc_id=p.acc_id)
  4            ) debit
  5  from ABC_V p
  6  order by acc_id
  7  /
    ACC_ID ACC_COD      RECORD_LEVEL PARENT_ACC_ID      DEBIT
---------- ------------ ------------ ------------- ----------
         1 2                       1             0
         2 2.01                    2             1
         3 2.01.01                 3             2        500
         4 2.01.01.0001            4             3        500
         5 2.02                    2             1
         6 2.02.01                 3             5        700
         7 2.02.01.0001            4             6        100
         8 2.02.01.0002            4             6        600
         9 2.02.02                 3             5        700
        10 2.02.02.0001            4             9        200
        11 2.02.02.0002            4             9        100
        12 2.02.02.0003            4             9        400

But I don't understand:

Quote:
Value For Acc_Id 5 Will Be 1600 and so on.

Explain.

Regards
Michel
Re: Need Sql Query [message #570737 is a reply to message #570736] Thu, 15 November 2012 03:15 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

   ACC_ID ACC_COD      RECORD_LEVEL PARENT_ACC_ID      DEBIT
---------- ------------ ------------ ------------- ----------
         1 2                       1             0       1900
         2 2.01                    2             1        500
         3 2.01.01                 3             2        500
         4 2.01.01.0001            4             3        500
         5 2.02                    2             1       1400
         6 2.02.01                 3             5        700
         7 2.02.01.0001            4             6        100
         8 2.02.01.0002            4             6        600
         9 2.02.02                 3             5        700
        10 2.02.02.0001            4             9        200
        11 2.02.02.0002            4             9        100
        12 2.02.02.0003            4             9        400

please check i have filled column with required value. debit column value must have sum of debit whose parent_acc_id is acc_id.
Look Acc_Id 1 and 5 has parent_Acc_id 1 so Debit column value for acc_id 1 must be 1900 which is sum of 1 and 5 column's debit value.

Regards,
Asif.
Re: Need Sql Query [message #570738 is a reply to message #570737] Thu, 15 November 2012 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from ABC_V p order by acc_id;
    ACC_ID ACC_COD      RECORD_LEVEL PARENT_ACC_ID      DEBIT
---------- ------------ ------------ ------------- ----------
         1 2                       1             0
         2 2.01                    2             1
         3 2.01.01                 3             2
         4 2.01.01.0001            4             3        500
         5 2.02                    2             1
         6 2.02.01                 3             5
         7 2.02.01.0001            4             6        100
         8 2.02.01.0002            4             6        600
         9 2.02.02                 3             5
        10 2.02.02.0001            4             9        200
        11 2.02.02.0002            4             9        100
        12 2.02.02.0003            4             9        400

The only acc_id that have 5 as parent are 6 and 9 which have no debit and so debit for 5 is NULL.

Regards
Michel
Re: Need Sql Query [message #570739 is a reply to message #570738] Thu, 15 November 2012 03:53 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

Yes, Sum Of Debit Of Acc_Id 6 and 9 will be debit value of 5. Now Acc_id 9 had Null debit value, First we will calculate 9 Acc_id Value by summing value of acc_id 10,11,12 because 10,11,12 has parent acc_id 9,after calculating value of 9 we will calculate value of 5 acc_id,

Acc_Id 9 value will be 700 by summing acc_id 10,11,12 (because parent_Acc_Id is 9)
acc_id 6 value will be 700 by summing acc_id 8,7 (because parent_Acc_Id is 6)
acc_id 5 value will be 1400 by summing acc_id 6,9 (because parent_Acc_Id is 5)

Regards,
Asif.
Re: Need Sql Query [message #570746 is a reply to message #570738] Thu, 15 November 2012 05:35 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle,
Have you understood my problem.

Regards,
Asif.
Re: Need Sql Query [message #570753 is a reply to message #570746] Thu, 15 November 2012 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes I did post the solution but it is no more there!
Will find it again and repost.

Regards
Michel
Re: Need Sql Query [message #570754 is a reply to message #570753] Thu, 15 November 2012 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here it is:
SQL> select acc_id, acc_cod, record_level, parent_acc_id,
  2         nvl(debit,
  3             (select sum(debit) from ABC_V c 
  4              connect by prior acc_id = parent_acc_id
  5              start with c.parent_acc_id=p.acc_id)
  6            ) debit
  7  from ABC_V p
  8  order by acc_id
  9  /
    ACC_ID ACC_COD      RECORD_LEVEL PARENT_ACC_ID      DEBIT
---------- ------------ ------------ ------------- ----------
         1 2                       1             0       1900
         2 2.01                    2             1        500
         3 2.01.01                 3             2        500
         4 2.01.01.0001            4             3        500
         5 2.02                    2             1       1400
         6 2.02.01                 3             5        700
         7 2.02.01.0001            4             6        100
         8 2.02.01.0002            4             6        600
         9 2.02.02                 3             5        700
        10 2.02.02.0001            4             9        200
        11 2.02.02.0002            4             9        100
        12 2.02.02.0003            4             9        400

Regards
Michel
Re: Need Sql Query [message #570802 is a reply to message #570754] Thu, 15 November 2012 23:37 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

Great job. Excellent.
Re: Need Sql Query [message #570807 is a reply to message #570802] Fri, 16 November 2012 00:43 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle,
May you reffer me any article which can clear me about "Connect By" and "Connect By Perior".

Regards,
Asif.
Re: Need Sql Query [message #570810 is a reply to message #570807] Fri, 16 November 2012 01:31 Go to previous message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can start with Hierarchical Queries in SQL Reference.

Regards
Michel
Previous Topic: traverse a table
Next Topic: inserting more than 30000 records into a table
Goto Forum:
  


Current Time: Tue Sep 02 19:39:02 CDT 2014

Total time taken to generate the page: 0.12328 seconds