OraFAQ Forum: SQL & PL/SQL » Need Sql Query

Home » SQL & PL/SQL » SQL & PL/SQL » Need Sql Query (10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Need Sql Query [message #570734]

Thu, 15 November 2012 01:48

mamalik
Messages: 247
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

Report message to a moderator

Re: Need Sql Query [message #570735 is a reply to message #570734]

Thu, 15 November 2012 02:08

Michel Cadot
Messages: 54194
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

Report message to a moderator

Re: Need Sql Query [message #570736 is a reply to message #570734]

Thu, 15 November 2012 02:16

Michel Cadot
Messages: 54194
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

Report message to a moderator

Re: Need Sql Query [message #570737 is a reply to message #570736]

Thu, 15 November 2012 03:15

mamalik
Messages: 247
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.

Report message to a moderator

Re: Need Sql Query [message #570738 is a reply to message #570737]

Thu, 15 November 2012 03:37

Michel Cadot
Messages: 54194
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

Report message to a moderator

Re: Need Sql Query [message #570739 is a reply to message #570738]

Thu, 15 November 2012 03:53

mamalik
Messages: 247
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.

Report message to a moderator

Re: Need Sql Query [message #570746 is a reply to message #570738]

Thu, 15 November 2012 05:35

mamalik
Messages: 247
Registered: November 2008
Location: Pakistan

Senior Member

Dear Michle,
Have you understood my problem.

Regards,
Asif.

Report message to a moderator

Re: Need Sql Query [message #570753 is a reply to message #570746]

Thu, 15 November 2012 06:14

Michel Cadot
Messages: 54194
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

Report message to a moderator

Re: Need Sql Query [message #570754 is a reply to message #570753]

Thu, 15 November 2012 06:15

Michel Cadot
Messages: 54194
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

Report message to a moderator