| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle query assistence
On Nov 14, 7:14 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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_thre...
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc
If you are certain that there will be no missing months, you can take
a shortcut using analytical functions:
SELECT
T.PRODUCT_NAME,
T.NUM_CASES,
ROW_NUMBER() OVER (PARTITION BY T.PRODUCT_NAME ORDER BY
T.REPORT_DATE) POSITION
FROM
T1 T;
SUBSTR(T NUM_CASES POSITION
-------- ---------- ----------
Product1 429 1 Product1 238 2 Product1 304 3 Product1 284 4 Product1 271 5 Product1 352 6 Product1 422 7 Product1 242 8 Product1 220 9 Product1 211 10 Product1 247 11 Product2 176 1 Product2 152 2 Product2 157 3 Product2 126 4 Product2 139 5 Product2 156 6 Product2 206 7 Product2 206 8 Product2 262 9 Product2 213 10 Product2 187 11
And then jump to the partial solution as before:
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)) P12FROM
Product1 429 238 304 247 Product2 176 152 157 187
Note that P1 now has a value and P12 does not.
Kind of make you wonder if this confusing SQL statement will also
work:
SELECT
T.PRODUCT_NAME,
MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
DD') - 365, 0),T.NUM_CASES,NULL)) P1,
MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
DD') - 365, 1),T.NUM_CASES,NULL)) P2,
MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
DD') - 365, 2),T.NUM_CASES,NULL)) P3,
MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
DD') - 365, 10),T.NUM_CASES,NULL)) P11,
Product1 429 238 211 247 Product2 176 152 213 187
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 14 2007 - 06:39:45 CST
 |
 |