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 return query output in html format? : Sql dba
What is java sql connection?
Is it possible to sort a column using a column alias?
How do sql databases work?
Types of joins ?
who introduced sql?
explain access control lists. : Sql dba
Types of indexes. What is the default key created when a primary key is created in a table ?
What are the types of keys?
what are the advantages of primary key over unique+notnull
7 Answers Consultancy, LGS, Oracle,
how to create a test table in your mysql server? : Sql dba
What is autocommit sql?