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 -> Re: Oracle query assistence

Re: Oracle query assistence

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 14 Nov 2007 04:14:52 -0800
Message-ID: <1195042492.207747.225810@22g2000hsm.googlegroups.com> 





On Nov 13, 7:46 pm, trp..._at_gmail.com wrote:


> I have a table defined as follows:

>

> CREATE TABLE products

> (

>    report_date  DATE             not null,

>    product_name varchar2(128)    not null,

>    num_cases    number(4)        not null,

>    CONSTRAINT peoplesoftProduct_pk PRIMARY KEY (report_date,

> product_name)

>  );

>

> Here is a query I am running that produces the data I am after:

>

> SELECT PRODUCT_NAME, NUM_CASES,


> to_char(REPORT_DATE,'Mon YYYY') AS "Report Date"

> FROM PRODUCTS


> WHERE PRODUCT_NAME IN ('Product1', 'Product2')

> AND report_date < to_date ('2007-09-30','YYYY-MM-DD')

> AND report_date > to_date ('2007-09-30','YYYY-MM-DD') - 365

> ORDER BY PRODUCT_NAME ASC, REPORT_DATE ASC


>

> Here is the output:

>

> PRODUCT_NAME NUM_CASES Report Date

> Product1        429     Nov 2006

> Product1        238     Dec 2006

> Product1        304     Jan 2007

> Product1        284     Feb 2007

> Product1        271     Mar 2007

> Product1        352     Apr 2007

> Product1        422     May 2007

> Product1        242     Jun 2007

> Product1        220     Jul 2007

> Product1        211     Aug 2007

> Product1        247     Sep 2007

> Product2        176     Nov 2006

> Product2        152     Dec 2006

> Product2        157     Jan 2007

> Product2        126     Feb 2007

> Product2        139     Mar 2007

> Product2        156     Apr 2007

> Product2        206     May 2007

> Product2        206     Jun 2007

> Product2        262     Jul 2007

> Product2        213     Aug 2007

> Product2        187     Sep 2007

>

> The way I actually want the data to display from a query is as

> follows:

>

>         Nov 2006                        Dec 2006                        Jan

> 2007                    ...

> Product1        429             238             304             ...

> Product2        176             152             157             ...

>

> Now I know I could use the above query and write a script to process

> the data to make it output as I wish, but I really want to do this in

> a query. Is this possible and how would I adapt the above query to do

> so?

>

> Thanks!




Let's take a look to see if there are ways to create cross-tab style
reports.



First, an experiment to see if we can have Oracle count the months,
starting with a particular month start date:
SELECT



  LEVEL POSITION,



  ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)

MONTH_START



FROM



  DUAL



CONNECT BY



  LEVEL<=12;



  POSITION MONTH_STA


---------- ---------
         1 01-OCT-06
         2 01-NOV-06
         3 01-DEC-06
         4 01-JAN-07
         5 01-FEB-07
         6 01-MAR-07
         7 01-APR-07
         8 01-MAY-07
         9 01-JUN-07
        10 01-JUL-07
        11 01-AUG-07
        12 01-SEP-07





Now that we see that is possible, we should be able to use a
combination of MAX, DECODE, and GROUP BY to develop a solution.
First, the set up:


CREATE TABLE T1 (



  REPORT_DATE DATE,



  PRODUCT_NAME VARCHAR2(128),



  NUM_CASES NUMBER(4));


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',429,'01-NOV-2006');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',238,'01-DEC-2006');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',304,'01-JAN-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',284,'01-FEB-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',271,'01-MAR-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',352,'01-APR-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',422,'01-MAY-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',242,'01-JUN-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',220,'01-JUL-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',211,'01-AUG-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product1',247,'01-SEP-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',176,'01-NOV-2006');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',152,'01-DEC-2006');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',157,'01-JAN-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',126,'01-FEB-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',139,'01-MAR-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',156,'01-APR-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',206,'01-MAY-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',206,'01-JUN-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',262,'01-JUL-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',213,'01-AUG-2007');


INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES

('Product2',187,'01-SEP-2007');



Now, let's take the SQL statement that we previously used above and
join it to a simple select statement with our T1 table - we will slide
each into an inline view:


SELECT


  T.PRODUCT_NAME,
  T.REPORT_DATE,
  T.NUM_CASES,
  C.POSITION




FROM



  (SELECT



    PRODUCT_NAME,



    REPORT_DATE,



    NUM_CASES



  FROM



    T1) T,


  (SELECT



    LEVEL POSITION,



    ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)

MONTH_START



  FROM



    DUAL



  CONNECT BY



    LEVEL<=12) C


WHERE



  T.REPORT_DATE=C.MONTH_START;


PRODUCT_ REPORT_DA  NUM_CASES   POSITION


-------- --------- ---------- ----------
Product1 01-NOV-06        429          2
Product1 01-DEC-06        238          3
Product1 01-JAN-07        304          4
Product1 01-FEB-07        284          5
Product1 01-MAR-07        271          6
Product1 01-APR-07        352          7
Product1 01-MAY-07        422          8
Product1 01-JUN-07        242          9
Product1 01-JUL-07        220         10
Product1 01-AUG-07        211         11
Product1 01-SEP-07        247         12
Product2 01-NOV-06        176          2
Product2 01-DEC-06        152          3
Product2 01-JAN-07        157          4
Product2 01-FEB-07        126          5
Product2 01-MAR-07        139          6
Product2 01-APR-07        156          7
Product2 01-MAY-07        206          8
Product2 01-JUN-07        206          9
Product2 01-JUL-07        262         10
Product2 01-AUG-07        213         11
Product2 01-SEP-07        187         12





You will likely note that there is now a counter next to each row from
the T1 table - we will use this with DECODE, MAX and GROUP BY to
collapse all rows for a single PRODUCT_NAME into a single row, and
place each of the NUM_CASES into a separate column based on the value
of POSITION:


SELECT



  T.PRODUCT_NAME,


  MAX(DECODE(C.POSITION,1,T.NUM_CASES,NULL)) P1,
  MAX(DECODE(C.POSITION,2,T.NUM_CASES,NULL)) P2,
  MAX(DECODE(C.POSITION,3,T.NUM_CASES,NULL)) P3,
  MAX(DECODE(C.POSITION,4,T.NUM_CASES,NULL)) P4,
  MAX(DECODE(C.POSITION,5,T.NUM_CASES,NULL)) P5,
  MAX(DECODE(C.POSITION,6,T.NUM_CASES,NULL)) P6,
  MAX(DECODE(C.POSITION,7,T.NUM_CASES,NULL)) P7,
  MAX(DECODE(C.POSITION,8,T.NUM_CASES,NULL)) P8,
  MAX(DECODE(C.POSITION,9,T.NUM_CASES,NULL)) P9,
  MAX(DECODE(C.POSITION,10,T.NUM_CASES,NULL)) P10,
  MAX(DECODE(C.POSITION,11,T.NUM_CASES,NULL)) P11,
  MAX(DECODE(C.POSITION,12,T.NUM_CASES,NULL)) P12


FROM



  (SELECT



    PRODUCT_NAME,



    REPORT_DATE,



    NUM_CASES



  FROM



    T1) T,


  (SELECT



    LEVEL POSITION,



    ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)

MONTH_START



  FROM



    DUAL



  CONNECT BY



    LEVEL<=12) C


WHERE



  T.REPORT_DATE=C.MONTH_START



GROUP BY



  T.PRODUCT_NAME;


(following output is shortened by width):
PRODUCT_         P1         P2         P3        P11        P12

-------- ---------- ---------- ---------- ---------- ----------

Product1                   429        238        211        247
Product2                   176        152        213        187





There are of course other ways to accomplish the above - this is just
one method.  If you search this group (or the other
comp.databases.oracle.x groups) using Google, you will likely find
other examples, including this one:


  http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/b06766c6871b698b



Charles Hooper


IT Manager/Oracle DBA


K&M Machine-Fabricating, Inc.
Received on Wed Nov 14 2007 - 06:14:52 CST












Original text of this message













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