CODEWITHSUNDEEP
oracle-database
rajigill.online
rajigill.online

Reputation: 11

Edit Total line in Oracle report 1

The data structure:

sql-> desc t1

- List item
   p_code number,
   acc_date date ,
   debit number,
   credit number

Data inside my table:

sql-> select * from t1;

| p_code | acc_date | debit | credit |
| 001    | 01-01-15 | 100   | 25     |
| 001    | 02-01-15 | 0     | 125    |   
| 001    | 03-01-15 | 415   | 85     |

I would like to do something like this:

select * from t1  
where acc_date between :fromdate and :todate 
union all
    select p_code, (sum(nvl(debit,0))- sum(nvl(credit,0))) open_balance  
    from t1
    where acc_date < :fromdate
;

But, I can't figure out what are my mistakes.

Upvotes: 0

Views: 49

Answers (1)

Stawros
Stawros

Reputation: 935

Type and number of columns in union must be the same - 

select p_code, acc_date, debit, credit, null as open_balance
  from t1
 where acc_date between :fromdate and :todate
union all
select p_code, null as acc_date, null as debit, null as credit,
       (sum(nvl(debit, 0)) - sum(nvl(credit, 0))) open_balance
  from t1
 where acc_date < :fromdate

Upvotes: 3

Related Questions