Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Unions With Grouping Group Values
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!!" - MeReceived on Fri Sep 16 2005 - 13:29:39 CDT