Home » SQL & PL/SQL » SQL & PL/SQL » carry value (oracle 10g)
carry value [message #448789] Thu, 25 March 2010 04:11 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

dear All
create table ora_faq(dpt_num number,
                     opn_bal number,
                     sub_amt number)
                     
insert into ora_faq values(1,5,1);

insert into ora_faq values(1,5,2);

insert into ora_faq values(1,5,2);

insert into ora_faq values(2,6,1);

insert into ora_faq values(2,6,2);

insert into ora_faq values(2,6,2);


when i query, i found data like
DPT_NUM	OPN_BAL	SUB_AMT

1	5	1
1	5	2
1	5	2
2	6	1
2	6	2
2	6	2


i need query which return follwoing result
DPT_NUM	OPN_BAL	SUB_AMT closing_Balance

1	5	1           4
1	5	2           2
1	5	2           0 
2	6	1           5
2	6	2           3
2	6	2           1



closing balance columne is calculated for each deptment,in first row opn_bal-Sub_Amt and in second row Closing_balance is carried farwarded and sub_amt is substracted.

Thanks in advance.
Asif.
Re: carry value [message #448793 is a reply to message #448789] Thu, 25 March 2010 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In which order must we take the rows?

Have a look at SUM in its analytic form.

Regards
Michel

[Updated on: Thu, 25 March 2010 04:35]

Report message to a moderator

Re: carry value [message #448798 is a reply to message #448789] Thu, 25 March 2010 04:25 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Order By Dpt_Num or Row_Num
Re: carry value [message #448802 is a reply to message #448798] Thu, 25 March 2010 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Order By Dpt_Num or Row_Num

There is no Row_Num column and Dpt_Num is not unique so the order is not deterministic.

Regards
Michel

[Updated on: Thu, 25 March 2010 04:36]

Report message to a moderator

Re: carry value [message #448810 is a reply to message #448789] Thu, 25 March 2010 05:02 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle,

Please consider follwoing query

SELECT row_number() over (partition by dpt_num order by dpt_num) row_num,dpt_num,opn_bal,sub_amt 
FROM ORA_fAQ


it will return data like
ROW_NUM	DPT_NUM	OPN_BAL	SUB_AMT
1	1	5	1
2	1	5	2
3	1	5	2
1	2	6	1
2	2	6	2
3	2	6	2


Re: carry value [message #448811 is a reply to message #448789] Thu, 25 March 2010 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or it could return data like this:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> SELECT row_number() over (partition by dpt_num order by dpt_num) row_num,dpt_num,opn_bal,sub_amt 
  2  FROM ORA_fAQ;

   ROW_NUM    DPT_NUM    OPN_BAL    SUB_AMT
---------- ---------- ---------- ----------
         1          1          5          2
         2          1          5          2
         3          1          5          1
         1          2          6          1
         2          2          6          2
         3          2          6          2

6 rows selected.


Remember data is not stored in the table in any particular order. If you don't specify an order by that is unique for the partition you'll get inderminate results.
Re: carry value [message #448813 is a reply to message #448789] Thu, 25 March 2010 05:18 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
another good pointer by Kevin SUM
Re: carry value [message #448814 is a reply to message #448810] Thu, 25 March 2010 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to realize that "order by dpt_num" does not determine a deterministic order as for each dpt_num you have several rows that can appear in any order each time you call the query.

Regards
Michel
Re: carry value [message #448823 is a reply to message #448789] Thu, 25 March 2010 05:29 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Michle Uncle,
I am unable to understand your reply. Have you understood my problem? what is best solution ?


Regards.
Asif.
Re: carry value [message #448827 is a reply to message #448789] Thu, 25 March 2010 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
The best (and only) solution is to have something to uniquely order the data. You do not appear to have that. Without a way of uniquely ordering the rows it is impossible to calculate the closing balance correctly.
Re: carry value [message #448828 is a reply to message #448823] Thu, 25 March 2010 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Have you understood my problem? what is best solution ?

Yes, no one can answer until you define a deterministic order.

Quote:
I am unable to understand your reply.

"order by dpt_num" means order the rows in the order of their dpt_num.
If several rows have the same dtp_num, how could they decide what is their order?

An example, take all the people in your building and tell them: "let you sort by eyes color and come".
Then tell them to go back to their seat and ask again for the same query.
What is the likelyhood that the second time you have people in the same order?

Regards
Michel
Re: carry value [message #448829 is a reply to message #448789] Thu, 25 March 2010 05:46 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle,

If i generate view by using follwing query

SELECT row_number() over (partition by dpt_num order by dpt_num) row_num,
    dpt_num,opn_bal,sub_amt  
FROM ORA_fAQ;


then data will be like
   ROW_NUM    DPT_NUM    OPN_BAL    SUB_AMT
---------- ---------- ---------- ----------
         1          1          5          2
         2          1          5          2
         3          1          5          1
         1          2          6          1
         2          2          6          2
         3          2          6          2

then which query will be for calculating closing balance?

Thanks for your time.

Regards.
Asif.

[Updated on: Thu, 25 March 2010 06:33] by Moderator

Report message to a moderator

Re: carry value [message #448831 is a reply to message #448829] Thu, 25 March 2010 05:56 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
mamalik wrote on Thu, 25 March 2010 10:46
then data will be like
   ROW_NUM    DPT_NUM    OPN_BAL    SUB_AMT
---------- ---------- ---------- ----------
         1          1          5          2
         2          1          5          2
         3          1          5          1
         1          2          6          1
         2          2          6          2
         3          2          6          2



It doesn't have to come back in that order.
It can come back in a completely different order.
Oracle does not (and can not) guarantee returning rows in the order in which they are inserted


mamalik wrote on Thu, 25 March 2010 10:46

then which query will be for calculating closing balance?

None. you need to give us a way of uniquely ordering the data. You haven't done that and until you do there is NO solution.
We have explained this several times already in multiple ways. Please pause for a moment and think about what we have told you.
Re: carry value [message #448836 is a reply to message #448789] Thu, 25 March 2010 06:11 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

   ROW_NUM    DPT_NUM    OPN_BAL    SUB_AMT
---------- ---------- ---------- ----------
         1          1          5          2
         2          1          5          2
         3          1          5          1
         1          2          6          1
         2          2          6          2
         3          2          6          2


assume above data is shown in a table and a row_num is table column and value is saved it while inserting record in table now order by clause is
order by dpt_num,row_num

now what will be query???

Regards,
Asif.
Re: carry value [message #448843 is a reply to message #448836] Thu, 25 March 2010 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now what will be query???

What is your problem then using SUM analytic function?
What is the balance?


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 25 March 2010 06:39]

Report message to a moderator

Re: carry value [message #448848 is a reply to message #448789] Thu, 25 March 2010 06:40 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Some query like
SELECT DPT_NUM,OPN_BAL,SUB_AMT, OPN_BAL-(SUM (SUB_AMT) OVER (PARTITION BY DPT_NUM ORDER BY RN)) FROM
(
SELECT DPT_NUM,OPN_BAL,SUB_AMT,ROWNUM AS RN FROM   ora_faq ORDER BY DPT_NUM
);

might resolve your problem
Re: carry value [message #448849 is a reply to message #448848] Thu, 25 March 2010 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And may give a different result each time you execute the query.

Regards
Michel
Re: carry value [message #448850 is a reply to message #448789] Thu, 25 March 2010 06:44 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
I agree that it might give the indeterministic output, but then the user has to define some mechanism to get deterministic RN in the above case.
In above example timestamp of sub_amt if given along with the dept no would have been best to order it by

[Updated on: Thu, 25 March 2010 06:46]

Report message to a moderator

Re: carry value [message #448853 is a reply to message #448850] Thu, 25 March 2010 06:46 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
maheshmhs wrote on Thu, 25 March 2010 11:44
I agree that it might give the indeterministic output, but then the user has to define some mechanism to get deterministic RN in the above case.


Which is what we've spent the last hour or so pointing out, so your solution really just confused matters.
Re: carry value [message #448854 is a reply to message #448789] Thu, 25 March 2010 06:48 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
With whatever is provided i believe you can't find the deterministic solution.
Re: carry value [message #448855 is a reply to message #448789] Thu, 25 March 2010 06:48 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

thanks a lot dear michle unlce and maheshmhs. It has clearned me and my concept deterministic RN is also clear now.

thanks a lot dear for your time .
Re: carry value [message #448868 is a reply to message #448855] Thu, 25 March 2010 07:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
mamalik wrote on Thu, 25 March 2010 07:48
It has clearned me and my concept deterministic RN is also clear now.


I doubt that very much. You saw an answer posted by maheshmhs that happened to work in a single case and decided that is the correct answer that will work in every case.
Re: carry value [message #448898 is a reply to message #448789] Thu, 25 March 2010 11:09 Go to previous message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear joy_division
I have found a direction to move by maheshmhs , now i will change according to my scenario.


Regards.
Asif.
Previous Topic: extract only number part of column
Next Topic: Case & Decode
Goto Forum:
  


Current Time: Mon Dec 05 18:57:35 CST 2016

Total time taken to generate the page: 0.13512 seconds