Hello All, Could any well write a query for the following
scenario.
Account(table name)
No Name Amount
1 ABCD 2000.00
2 DEFG -2000.00
3 GHIJ 3000.50
4 JKLM 4000.00
5 MNOP 6000.00
O/p Should be in this format
No Name Credit Debit
1 ABCD 2000.00 0
2 DEFG 0 -2000.00
3 GHIJ 3000.50
4 JKLM 0 -4000.00
5 MNOP 6000.00 o
could any one give appropriate query for this
Thnks in Advance
Suneel Reddy
Answers were Sorted based on User's Feedback
Answer / mkumar.it
select no, name,
case amount/abs(amount) when 1 then amount else 0 end credit,
case amount/abs(amount) when -1 then amount else 0 end Debit
from account
Is This Answer Correct ? | 10 Yes | 0 No |
Answer / saravana sundar
select sno,name,decode(sign(amount),1,0,amount)debit,decode
(sign(amount),-1,0,amount)credit from account
/
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / ehtesham malik
Select no,name, 0 Debit, amount Credit
from temp
where amount >0
union
Select no,name, amount Debit, 0 Credit
from temp
where amount < 0
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / shailesh
SELECT NO,NAME,DECODE(GREATEST
(AMOUNT,0),0,AMOUNT,0) "DEBIT",DECODE(GREATEST
(AMOUNT,0),0,0,AMOUNT) "CREDIT" FROM ACCOUNT
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / john bershan
@ mkumar.it
the query which you have posted will work if the amount
value is greater than zero and will not work in the case
when the vale s zero as it will throw divisor is equal to
zero.please check it.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / suresh kumar somayajula
Select No,Name,to_char(Sal,'$9999.99') amount from emp;
Is This Answer Correct ? | 0 Yes | 12 No |
how to create table with in the procedure or function?
What is a clob in sql?
How to perform a loop through all tables in pl/sql?
Can we rollback after truncate?
What is sql injection vulnerability?
What is a delimiter in sas?
What is nosql example?
What is pivot table in sql?
What do you mean by stored procedures?
Is sql an operating system?
Where the sql database files are stored?
How can we optimize a sql query?