Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Unions With Grouping Group Values

Unions With Grouping Group Values

From: Tim Marshall <TIMMY!_at_PurplePandaChasers.Moertherium>
Date: Fri, 16 Sep 2005 15:59:39 -0230
Message-ID: <dgf2uj$ci5$1@coranto.ucs.mun.ca>


I am dealing with an application in which I have a table for work orders
(which describes jobs performed) and three separate tables for
transaction against the work order rows, one for labour, one for parts and another for other charges.

What I am trying to do is create an output that shows me:

Building     Fiscal Year     Labour     Parts     Other Charges
Building 1    2001            $100       $200      $50
Building 1    2002            $200       $230      $90

etc.

I'm using a union query, a simplified example of which I have below
(I've omitted other tables and joins and have only included two select
statements for labour and other charges instead of the three for labour, parts, other charges). However, what it gives me is something like:

Building     Fiscal Year     Labour     Parts     Other Charges
Building 1    2001            $100       0          0
Building 1    2002            $200       0          0
Building 1    2001            0          $200       0
Building 1    2002            0          $230       0
Building 1    2001            0          0          $50
Building 1    2002            0          0          $90

Is there anyway I can use Oracle SQL to achieve what I want? Am I approaching this the wrong way? I don't wish to be spoonfed, so if there is an obvious technique I am missing here just let me know what it is and I can research it on my own.

Thanks very much in advance and I hope the length of the statement below is not excessive for a news group post.

select
FB_BLDG_NAME "Building", sum(FWOR_EXT_COST) "Labour", 0 "Other", to_char(fwor_trans_date, 'YYYY') "Fiscal Year" from
tma.f_workorder, tma.f_wo_labor
where
fwor_trans_date >= to_date('1-APR-2002') and fwor_trans_date <= to_date('1-SEP-2005') and wo_pk = fwor_wo_fk group by
FB_BLDG_NAME, to_char(fwor_trans_date, 'YYYY') union
select
FB_BLDG_NAME "Building", 0 "Labour", sum(ex_inv_amt) "Other", to_char(fwor_trans_date, 'YYYY') "Fiscal Year" from
tma.f_workorder, tma.f_wo_charges
where
ex_trans_date >= to_date('1-APR-2002') and ex_inv_date <= to_date('1-SEP-2005') and wo_pk =ex_wo_fk group by
FB_BLDG_NAME, to_char(fwor_trans_date, 'YYYY')

-- 
Tim Marshall
Manager Work Control (709) 737-2662
Facilities Management, Memorial University
St. John's NL Canada

-- 
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me
Received on Fri Sep 16 2005 - 13:29:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US