Hi Suneel, SELECT * FROM ACCOUNT; NO NAME AMOUNT ----- -

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

Question Posted / srinu

5 Answers
7292 Views
Target, I also Faced
E-Mail Answers

Answer Posted / srinu

Hi Suneel,

SELECT * FROM ACCOUNT;

NO NAME AMOUNT
----- ----- ----------
1 ABCD 2000.00
2 DEFG -2000.00
3 GHIJ 3000.00
4 JKLM -4000.00
5 MNOP 5000.00

SELECT NO,NAME,DECODE(SIGN(AMOUNT),1,TO_CHAR(AMOUNT,'9999.99'),-1,0) CREDIT,
DECODE(SIGN(AMOUNT),-1,TO_CHAR(AMOUNT,'9999.99'),1,0) DEBIT FROM ACCOUNT
/

NO NAME CREDIT DEBIT
------ ----- -------- --------
1 ABCD 2000.00 0
2 DEFG 0 -2000.00
3 GHIJ 3000.00 0
4 JKLM 0 -4000.00
5 MNOP 5000.00 0

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What are sql objects?

1067

How does cross join work in sql?

1003

What is the difference between sql and isql*plus?

1075

what are the differences between procedure-oriented languages and object-oriented languages? : Sql dba

1027

what is log shipping? : Sql dba

1095

What information is needed to connect sql*plus an oracle server?

1008

How do I find duplicates in two columns?

1136

Why trigger is used in sql?

953

What is attribute indicator in pl sql?

1001

How do sql triggers work?

1024

what is oltp (online transaction processing)? : Sql dba

1067

What is an escape character in sql?

1011

What is pl sql block structure?

1018

What is record variable?

965

What is exception? What are the types of exceptions?

1062