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 |
what is a trigger in mysql? Define different types of trigger. : Sql dba
what is the difference between clustered and non clustered index in sql? : Sql dba
Why plvtab is considered as the easiest way to access the pl/sql table?
in sql table follw columns r their vehicle_no|servicetype|date|location| 1234 |1FS |1-JAN|PUNE| 4561 |2FS |5-JAN|PUNE| 1234 |PS |7-JAN|PUNE| 1234 |PS |15-JAN|MUMB| i want o/p below vehicleno|1fs|1fsdate|1fslocation|1ps|1PSDATE|1PSLOC|2PS|2PS DATE|2PSLOCA e.g 1234|1FS|1JAN|PUNE|1PS|7JAN|PUNE|2PS|15JAN|MUMBAI PLS help me out to execute using sql query
What is trigger in flip flop?
State the advatage and disadvantage of Cursor's?
in oracle 10g sw after compiling procedure how to pass parameter values ,if we (v_empid out number)how to give empid after successful compilation program.This site exact suitable for 10g with respect to question & answer same format , im trying sql browser & sql command prompt using exec procedure name & respective parameters.
How can you save or place your msg in a table?
What is int identity in sql?
What is native sql query?
How to call the function and Procedure in trigger?
I have one table and column is c1 pk, c2 not null,c3 not null and 200 row in it and i add c4 column and value, how can is possible