Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Case (Oracle 9i)
Regarding Case [message #302599] Tue, 26 February 2008 05:17 Go to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
I have data in the below mentioned format

stocknumber transtype transsubtype balance

s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100

s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300


I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below

stocknumber transgroup balance
s01 (out,cust) 300
s01 (in,deal) 300
s01 (in,cust) 100
s02 (out,cust) 500
s02 (in,deal) 200
s02 (in,cust) 400

I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .




Re: Regarding Case [message #302633 is a reply to message #302599] Tue, 26 February 2008 06:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please put [code]...[/code] tags around your demo in order to preserve formatting. Without this, most people will not be able to (or even try to) decipher your issue.
Re: Regarding Case [message #302639 is a reply to message #302599] Tue, 26 February 2008 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why a simple GROUP BY does not work?

Regards
Michel
Re: Regarding Case [message #302679 is a reply to message #302599] Tue, 26 February 2008 10:30 Go to previous messageGo to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
I am sorry for the inconvenience caused. I am re posting my message with a lttle change as per my requirement.
I have data in the below mentioned format
stocknumber  transtype     transsubtype     balance

s01           in              cust          100
s01           out             cust          200
s01           in              deal          300
s01           out             cust          100

s02           in               deal          200
s02           out             cust           300
s02           in              cust           100
s02           out             cust           200
s02           in             cust           300 



I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below

stocknumber  in,deal   out,cust  in,cust     
s01             300      300      100
s02             200      500      400  




I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .

Re: Regarding Case [message #302681 is a reply to message #302679] Tue, 26 February 2008 10:34 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did, the result you got and why it does fit your requirements.

Regards
Michel
Previous Topic: get the second highest salary
Next Topic: CASE statement with Exists clause
Goto Forum:
  


Current Time: Thu Dec 08 04:06:38 CST 2016

Total time taken to generate the page: 0.09547 seconds