Home » SQL & PL/SQL » SQL & PL/SQL » Compute
Compute [message #19219] Tue, 05 March 2002 14:44 Go to next message
Fari
Messages: 5
Registered: March 2002
Junior Member
I WANT TO ADD ALL THE 10 FIELDS UP AND SUBTRACT FROM AC1.IF THE ANSWER AFTER ITS SUBTRACTED FROM AC1 IS ZERO IT MUST IGNORE BUT IF ITS > THAN 1 THEN SUBTRACT. I DONT KNOW HOW TO WRITE THE SYNTAX HELP

select
AC1 - sum(AB1+AB2+AB3+AB4+AB5+AB6+AB7+AB8+AB9+AB10)
from test
Re: Compute [message #19230 is a reply to message #19219] Wed, 06 March 2002 05:50 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
try this
select
decode
( AC1 - (AB1+AB2+AB3+AB4+AB5+AB6+AB7+AB8+AB9+AB10),
0,AC1,AC1 -(AB1+AB2+AB3+AB4+AB5+AB6+AB7+AB8+AB9+AB10)
)
from test
Re: Compute [message #19268 is a reply to message #19230] Thu, 07 March 2002 13:50 Go to previous message
Rm69
Messages: 39
Registered: January 2002
Member
Key
-----
cap386 =capital
da..... =disbursements
bro386 = branch
dea386 =dealref

insert into Mytable(
total,bro386,brn386,dea386,count)

select
decode(CAP386(da1386+da2386+da3386+da4386+da5386+da6386+da7386+da8386+da9386+da0386),
0,CAP386,CAP386 -(da1386+da2386+da3386+da4386+da5386+da6386+da7386+da8386+da9386+da0386)
),bro386,brn386,dea386,count(da1386)
from test
where ah1386 like '%AFRICAN BANK%' and dea386='4000234840';

Q)when l run this sript it give me ora 00904:Invalid column name.Thats for column CAP386.
So after summing the da..etc l must subtract the amount from the capital.If its zero ignore if not compute.
Q)If l want to insert the answer after computing into column Total in table mytable is that feasible?
Previous Topic: Procedure
Next Topic: book website
Goto Forum:
  


Current Time: Wed Oct 27 05:31:36 CDT 2021