Last post Dec 06, 2010 02:02 AM by sandeepmittal11
Dec 03, 2010 11:02 AM|Mobzam|LINK
I am developing an web application using csharp and oracle as database. I have table with columns like below. How can I ensure that sql selects from one table column but it shows two columns depending weather it is Debit or credit. How can I come up with
something Statement of Account ?
Number Amount Type
2 6000 Dr
3 4500 Cr
4 2300 Dr
5 1200 Dr
6 1300 Cr
Now I want the sql out put to be as below. How can I achieve?
NUMBER Dr CR Balance
2 6000 1000
3 4500 5500
4 2300 3200
5 1200 2000
6 1300 3300
Dec 03, 2010 12:11 PM|sandeepmittal11|LINK
, CASE WHEN TYPE = 'Dr' THEN Amount ELSE NULL END AS "Dr"
, CASE WHEN TYPE = 'Cr' THEN Amount ELSE NULL END AS "Cr"
Dec 03, 2010 12:22 PM|kpyap|LINK
Please try SQL below:
Dr = case when a.type = 'Dr' then a.amount else null end,
Cr = case when a.type = 'Cr' then a.amount else null end,
(Select coalesce(Sum(case type when 'Dr' then amount* -1 else amount end), 0)
Where number <= a.number)
from soa a
Hope it works in Oracle as well.
Dec 05, 2010 09:08 PM|N Rajesh|LINK
Please check this,
Decode(type, 'Cr', Amount) Cr
Decode(type, 'Cr', Amount) Dr
Dec 06, 2010 02:02 AM|sandeepmittal11|LINK
, DECODE(TYPE, 'DR', AMOUNT)
, DECODE(TYPE, 'CR', AMOUNT)
, SUM (DECODE(TYPE, 'CR', AMOUNT, -1 * AMOUNT)) OVER (ORDER BY NUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BALANCE
GROUP BY NUM, TYPE, AMOUNT