Home » SQL & PL/SQL » SQL & PL/SQL » generate result row
generate result row [message #300346] Fri, 15 February 2008 02:28 Go to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Dear all,

I'm struggling with the following and hope one of you have a good suggestion to solve it!

Data is (simplified):

some_id    type    amount
1          costs      500
1          revenue    600
2          costs      100  <-- note: no revenue for id 2
3          costs      200
3          revenue    250


Desired output:

some_id    type    amount
1          costs      500
1          revenue    600
1          result     100  <-- generated result row
2          costs      100
2          result    -100  <-- generated result row
3          costs      200
3          revenue    250
3          result      50  <-- generated result row


I've tried decodes, case whens, cartesian joins to (select 'costs' from dual union select 'revenue' from dual union select 'result' from dual) and then eliminating the duplicate row for results, etc...
But can't find a nice, neat solution. Anyone??

Regards,
Sabine

[Updated on: Fri, 15 February 2008 02:30]

Report message to a moderator

Re: generate result row [message #300352 is a reply to message #300346] Fri, 15 February 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This might be a first step:
SQL> select * from t order by 1,2;

        ID TYP            AMOUNT
---------- ---------- ----------
         1 costs             500
         1 revenue           600
         2 costs             100
         3 costs             200
         3 revenue           250

5 rows selected.

SQL> select id, 
  2         decode(grouping(typ),1,'result',typ) typ,
  3         sum(decode(typ,'revenue',1,-1)*amount) amount
  4  from t
  5  group by rollup(id,typ)
  6  having grouping(id) = 0
  7  order by id, decode(typ,'result',2,1)
  8  /

        ID TYP            AMOUNT
---------- ---------- ----------
         1 costs            -500
         1 revenue           600
         1 result            100
         2 costs            -100
         2 result           -100
         3 costs            -200
         3 revenue           250
         3 result             50

8 rows selected.

Regards
Michel

[Updated on: Fri, 15 February 2008 03:02]

Report message to a moderator

Re: generate result row [message #300404 is a reply to message #300352] Fri, 15 February 2008 05:41 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Michel,
Exactly what I was looking for, brilliant! Thanks!
Regards,
Sabine
Re: generate result row [message #300609 is a reply to message #300404] Sat, 16 February 2008 13:11 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
>=10g

SQL> with t as ( select 1 some_id, 'costs'     type,  500  amount from dual union all
  2              select 1 some_id, 'revenue'   type,  600  amount from dual union all
  3              select 2 some_id, 'costs'     type,  100  amount from dual union all
  4              select 3 some_id, 'costs'     type,  200  amount from dual union all
  5              select 3 some_id, 'revenue'   type,  250  amount from dual)
  6  --
  7  select * from t
  8   model
  9    partition by (some_id)
 10    dimension by (type)
 11    measures(amount)
 12    (amount['result']=nvl(amount['revenue'],0)-nvl(amount['costs'],0))
 13  /

   SOME_ID TYPE        AMOUNT
---------- ------- ----------
         1 costs          500
         1 revenue        600
         1 result         100
         2 costs          100
         2 result        -100
         3 costs          200
         3 revenue        250
         3 result          50

8 rows selected

SQL> 


-----------
VB

[Updated on: Sat, 16 February 2008 13:43] by Moderator

Report message to a moderator

Re: generate result row [message #301475 is a reply to message #300609] Wed, 20 February 2008 16:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
@Volder, are you suggesting that casting to a multi-dimensional array is a superior method to ROLLUP() for generating sub-totals?

Could you explain your reasoning?

Ross Leishman
icon1.gif  Re: generate result row [message #310634 is a reply to message #300346] Tue, 01 April 2008 20:28 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi all
I have some hard work to improve if someone could help.
looking for procedures to:
Mechanism to calculate purchase total based on seats =>like in Theatre system (have tables Purchase and seat).
VAT calculations.
Overtime calculations(I may need an attendence table for staff?)but which info to put in?.
Salaries with or without Overtime?
automatic generation of overtime records?
the last one
Procedures to create new staff records with appropriate values when staff are appointed.
---------------------------------
tables created with relationship:
Customer
Purchase-->Purchase_Seat--> Seat-->Event--> Venue
Staff--> Overtime_session-->Payment.

all comments are welcome, Thanks


Re: generate result row [message #310635 is a reply to message #300346] Tue, 01 April 2008 20:51 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
rolmau,

Why did you not read & FOLLOW posting guidelines as stated below?

Before you post anything in this forum be aware that this forum is meant for Expert SQL problems.
This does NOT mean that experts only read this forum; 99% also read the Newbie-forum!

So, before posting here ask yourself the following:
- Do I consider myself an expert?
- Do I think the problem at hand is a difficult one?
- Is my problem strictly SQL-related?

if you answer at least one of the above questions with "No" or "Don't know", find the appropriate forum here at OraFAQ.
If all three are answered "Yes", you are most welcome to post here!

Why did you hijack another person's thread?

Your problem statement really has nothing to do with Oracle;
except the possibly the instructor expects you to use Oracle.
Your problem could be solved using any RDBMS.

Why did you not post in the Homework forum?
Previous Topic: where is my Directory Location in my HHD ?
Next Topic: parameterised cursor or nested cursor
Goto Forum:
  


Current Time: Sat Dec 10 08:57:49 CST 2016

Total time taken to generate the page: 0.09488 seconds