Re: Oracle query assistence

On Nov 14, 7:14 am, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Nov 13, 7:46 pm, trp...@xxxxxxxxx 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)) P12
FROM
(SELECT
T.PRODUCT_NAME,
T.NUM_CASES,
ROW_NUMBER() OVER (PARTITION BY T.PRODUCT_NAME ORDER BY
T.REPORT_DATE) POSITION
FROM
T1 T) T
GROUP BY
T.PRODUCT_NAME;

SUBSTR(T P1 P2 P3 P11 P12
-------- ---------- ---------- ---------- ---------- ----------
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,
MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
DD') - 365, 11),T.NUM_CASES,NULL)) P12
FROM
T1 T
GROUP BY
T.PRODUCT_NAME;

PRODUCT_ P1 P2 P3 P11 P12
-------- ---------- ---------- ---------- ---------- ----------
Product1 429 238 211 247
Product2 176 152 213 187

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

.

Relevant Pages

  • Re: Oracle query assistence
    ... ORDER BY PRODUCT_NAME ASC, REPORT_DATE ASC ... place each of the NUM_CASES into a separate column based on the value ... IT Manager/Oracle DBA ... ROW_NUMBEROVER (PARTITION BY T.PRODUCT_NAME ORDER BY ...
    (comp.databases.oracle.server)
  • Re: Analytics question maybe - combine over lapping date rows
    ... LEADOVER (PARTITION BY T1.KEY ORDER BY ... select key, start_dtm, end_dtm, max (grp) over (partition by key ... by key order by start_dtm asc) ... Then we use analytics again to fill in the blanks in the enclosing ...
    (comp.databases.oracle.server)
  • Re: maxrecords property doesnt work for me
    ... I just implemented your SQL statement and tested it. ... It isn't the same as the five lowest scores ... "ORDER BY q.CurrentDifferential ASC) As q1" ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Help filtering records
    ... I have a report I am running and I want to select a distict date. ... including the SQL statement and record select Formula info. Evertime I try ... INNER JOIN "READER" READER ON ... BADGE."HAZMAT" ASC, ...
    (microsoft.public.vb.crystal)