Re: Unions With Grouping Group Values

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 16 Sep 2005 12:55:01 -0700
Message-ID: <1126900453.102504@yasure>

Tim Marshall wrote:
> 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.

Another solution that might work is one I have demonstrated on my DECODE page in Morgan's Library: www.psoug.org. Here's one example of it:

  DECLARE posn PLS_INTEGER := 0;
empid PLS_INTEGER := 178;
x NUMBER;

BEGIN
   SELECT NVL(SUM(ah.quantity * ah.saleprice * ap.payoutpct), 0)    INTO x
   FROM accessoryhistory ah, payoutpercentage ap,    sku s, store st
   WHERE empid = DECODE(posn,

     0, st.areadir,
     1, st.areamgr,
     2, NVL(st.storemgr1, st.storemgr2),
     3, NVL(st.asstmgr1, NVL(st.asstmgr2,
     st.asstmgr3)))

   AND ah.statustype IN ('ACT', 'DEA')
   AND ah.store = st.store
   AND s.dbid = ah.dbid
   AND s.sku = ah.sku
   AND ap.productgroup = s.productgroup
   AND ap.position = posn;

   dbms_output.put_line(x);
END;
/

A simple modification would allow you to create columns from different tables.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Fri Sep 16 2005 - 14:55:01 CDT