Home » SQL & PL/SQL » SQL & PL/SQL » SQL Queries (merged)
SQL Queries (merged) [message #352414] Tue, 07 October 2008 09:13 Go to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
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

Rgds
Suneel Reddy

[Updated on: Tue, 07 October 2008 09:15]

Report message to a moderator

SQL Queries [message #352416 is a reply to message #352414] Tue, 07 October 2008 09:17 Go to previous messageGo to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
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

Rgds
Suneel Reddy
Re: SQL Querys [message #352418 is a reply to message #352414] Tue, 07 October 2008 09:25 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@suneelreddy,

I am confused with the output you have provided.

Quote:

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



Please explain what you actually require, what you have tried with the help of some test cases.

Please read OraFaq Forum Guide before posting.

Regards,
Jo

[Updated on: Tue, 07 October 2008 09:28]

Report message to a moderator

Re: SQL Queries [message #352419 is a reply to message #352416] Tue, 07 October 2008 09:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
We won't give you free queries here. We will however try to help you get to the query yourself.
For your question, you probably will need the NVL function.
Re: SQL Querys [message #352420 is a reply to message #352418] Tue, 07 October 2008 09:32 Go to previous messageGo to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
Hi Jo,

Thnks for the response

o/p should contains 4 columns

no name credit debit


+ve values of amount column should come in credit and -ve values of amount should come in debit



i.e o/p should be


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 0
4 JKLM 0 -4000.00
5 MNOP 6000.00 o


i think this clariffied.

----------------------------------------------------------------


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 0
4 JKLM 0 -4000.00
5 MNOP 6000.00 o


could any one give appropriate query for this

Thnks in Advance
Re: SQL Querys [message #352423 is a reply to message #352420] Tue, 07 October 2008 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use DECODE or CASE to spread values among the 2 columns.

Regards
Michel
Re: SQL Queries [message #352425 is a reply to message #352416] Tue, 07 October 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question just carefully choose the appropriate forum.

Regards
Michel

[Updated on: Tue, 07 October 2008 10:11]

Report message to a moderator

Re: SQL Querys [message #352433 is a reply to message #352420] Tue, 07 October 2008 11:07 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@suneelreddy,

The hint is already given by @Michel. Try to go through the following links:

Decode Function
Case Statement

Hope this helps,

Regards,
Jo
icon14.gif  Re: SQL Querys [message #352442 is a reply to message #352433] Tue, 07 October 2008 12:41 Go to previous messageGo to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
Hi Jo/Michel,

Thanks for the clue i got the result by using case expression.
& one more doubt can we use relational operators in decode .

pls find the same.

SELECT NO,
CASE
WHEN Amount < 0 THEN Amount
WHEN Amount >= 0 THEN 0
END Credit,
CASE
WHEN Amount > 0 THEN Amount
WHEN Amount <= 0 THEN 0
END Debit
FROM lb

Re: SQL Querys [message #352447 is a reply to message #352442] Tue, 07 October 2008 13:22 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@suneelreddy,

No its not possible to use relational operators in Decode statement. Including a relational operator makes your expression a condition. Only expressions are allowed in DECODE statement.

Please go through the link of Decode Statement again. Note the use of SIGN Function in the examples there.

You can have an idea about SIGN Function by THIS LINK

Hope this helps,

Regards,
Jo

[Updated on: Tue, 07 October 2008 21:53]

Report message to a moderator

Previous Topic: How to keep sql code
Next Topic: unique field
Goto Forum:
  


Current Time: Sat Dec 03 12:22:29 CST 2016

Total time taken to generate the page: 0.07238 seconds