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: Mon, 26 Nov 2007 18:38:08 -0800 (PST)
Message-ID: <cf8de2d4-2ee1-4486-893e-318db2b43086@e10g2000prf.googlegroups.com>


On Nov 26, 6:47 pm, trp..._at_gmail.com wrote:
> On Nov 14, 5:39 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?
> > 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.
>
> Thanks for all your help, this is real close, only thing I need
> differently is the columns to display the month/year, so instead of
> P1,P2,P3,etc... it needs to be something like Nov 2006, Dec 2006, Jan
> 2007, etc...
>
> But the months cannot be a hard coded value, they need to be dynamic
> based on the associated REPORT_DATE.
>
> Any ideas??

You would need to modify the SQL statement prior to submitting it to the database in order to have the returned column names change to reflect the months. If your tool for submitting the query can handle this, it might be one option.

Another possibility is to retrieve the months on the first row of the result set, and then make certain that the column headings are not printed. It is possible to do that using a UNION ALL, for example the following (using the data rows inserted in a previous message in this thread):
SELECT
  'Product Name' "PRODUCT_NAME",
  TO_CHAR(MAX(DECODE(C.POSITION,1,C.MONTH_START,NULL)),'MON YYYY') P1,
  TO_CHAR(MAX(DECODE(C.POSITION,2,C.MONTH_START,NULL)),'MON YYYY') P2,
  TO_CHAR(MAX(DECODE(C.POSITION,3,C.MONTH_START,NULL)),'MON YYYY') P3,
  TO_CHAR(MAX(DECODE(C.POSITION,4,C.MONTH_START,NULL)),'MON YYYY') P4,
  TO_CHAR(MAX(DECODE(C.POSITION,5,C.MONTH_START,NULL)),'MON YYYY') P5,
  TO_CHAR(MAX(DECODE(C.POSITION,6,C.MONTH_START,NULL)),'MON YYYY') P6,
  TO_CHAR(MAX(DECODE(C.POSITION,7,C.MONTH_START,NULL)),'MON YYYY') P7,
  TO_CHAR(MAX(DECODE(C.POSITION,8,C.MONTH_START,NULL)),'MON YYYY') P8,
  TO_CHAR(MAX(DECODE(C.POSITION,9,C.MONTH_START,NULL)),'MON YYYY') P9,
  TO_CHAR(MAX(DECODE(C.POSITION,10,C.MONTH_START,NULL)),'MON YYYY') P10,
  TO_CHAR(MAX(DECODE(C.POSITION,11,C.MONTH_START,NULL)),'MON YYYY') P11,
  TO_CHAR(MAX(DECODE(C.POSITION,12,C.MONTH_START,NULL)),'MON YYYY') P12
FROM
  (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
UNION ALL
SELECT
  T.PRODUCT_NAME,

  TO_CHAR(MAX(DECODE(C.POSITION,1,T.NUM_CASES,NULL))) P1,
  TO_CHAR(MAX(DECODE(C.POSITION,2,T.NUM_CASES,NULL))) P2,
  TO_CHAR(MAX(DECODE(C.POSITION,3,T.NUM_CASES,NULL))) P3,
  TO_CHAR(MAX(DECODE(C.POSITION,4,T.NUM_CASES,NULL))) P4,
  TO_CHAR(MAX(DECODE(C.POSITION,5,T.NUM_CASES,NULL))) P5,
  TO_CHAR(MAX(DECODE(C.POSITION,6,T.NUM_CASES,NULL))) P6,
  TO_CHAR(MAX(DECODE(C.POSITION,7,T.NUM_CASES,NULL))) P7,
  TO_CHAR(MAX(DECODE(C.POSITION,8,T.NUM_CASES,NULL))) P8,
  TO_CHAR(MAX(DECODE(C.POSITION,9,T.NUM_CASES,NULL))) P9,
  TO_CHAR(MAX(DECODE(C.POSITION,10,T.NUM_CASES,NULL))) P10,
  TO_CHAR(MAX(DECODE(C.POSITION,11,T.NUM_CASES,NULL))) P11,
  TO_CHAR(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; The output of the above looks like this (shortened for width): following output is shortened by width): PRODUCT_ P1 P2 P3 P11 P12 -------- ---------- ---------- ---------- ---------- ---------- Product OCT 2006 NOV 2006 DEC 2006 AUG 2007 SEP 2007
Product1            429        238        211        247
Product2            176        152        213        187

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Nov 26 2007 - 20:38:08 CST

Original text of this message

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