Re: SQL Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 13 Feb 2008 09:42:46 -0800 (PST)
Message-ID: <36806549-2ece-4988-838d-f53d85cae2e3@l1g2000hsa.googlegroups.com>

On Feb 13, 10:41 am, NN <nav.n..._at_gmail.com> wrote:
> I have a table on Oracle
>
> Product Store   Day     Promotion_Days
> 1       A       01/01/2008      2
> 2       A       01/05/2008      3
> 1       B       01/09/2008      3
> 5       D       01/11/2008      2
> 9       F       01/21/2008      1
> 1       A       01/31/2008      3
>
> Now I want to expand the table to create a view and include a column
> that will include the dates the promotion is valid. The logic to
> calculate that is Day + Promotion Days so if you take row 1 as in the
> above table you should read it as - For Product 1 in Store A the
> Promotion starts on 01/01/2008 and lasts for 2 days 1.e until end of
> 01/03/2008
>
> Now my final Table or View will look like
>
> Product Store   Day     Promotion_Days  Promotion_Valid_On
> 1       A       01/01/2008      2       01/01/200/8
> 1       A       01/01/2008      2       01/02/2008
> 2       A       01/05/2008      3       01/05/2008
> 2       A       01/05/2008      3       01/06/2008
> 2       A       01/05/2008      3       01/07/2008
> 1       B       01/09/2008      3       01/09/2008
> 1       B       01/09/2008      3       01/10/2008
> 1       B       01/09/2008      3       01/11/2008
> 5       D       01/11/2008      2       01/11/2008
> 5       D       01/11/2008      2       01/12/2008
> 9       F       01/21/2008      1       01/21/2008
> 1       A       01/31/2008      3       01/31/2008
> 1       A       01/31/2008      3       02/01/2008
> 1       A       01/31/2008      3       02/02/2008
>
> I am stumped and any help will be appreciated. Thanks in advance.

First the setup:
CREATE TABLE T4(
  PRODUCT VARCHAR2(5),
  STORE VARCHAR2(5),
  PROMO_DATE DATE,
  PROMO_DAYS NUMBER(10)); INSERT INTO
  T4
VALUES(
  '1',
  'A',
  TO_DATE('01/01/2008','MM/DD/YYYY'),
  2);

INSERT INTO
  T4
VALUES(
  '2',
  'A',
  TO_DATE('01/05/2008','MM/DD/YYYY'),
  3);

INSERT INTO
  T4
VALUES(
  '1',
  'B',
  TO_DATE('01/09/2008','MM/DD/YYYY'),
  3);

What we need is a way to count up from 1 to the number of PROMO_DAYS and join that counter to the table. The following might create a useful counter:
SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

   COUNTER

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

With the counter created, we join it to our test table, making certain that the counter is less than or equal to the PROMO_DAYS: SELECT
  PRODUCT,
  STORE,

  PROMO_DATE,
  PROMO_DAYS,
  PROMO_DATE+(COUNTER-1) PROMO_VALID

FROM
  T4,
  (SELECT
    ROWNUM COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=300) C
WHERE
  PROMO_DAYS>=COUNTER
ORDER BY
  STORE,
  PRODUCT,
  PROMO_DATE,
  5;

The output looks like this:
PRODU STORE PROMO_DAT PROMO_DAYS PROMO_VAL

----- ----- --------- ---------- ---------
1     A     01-JAN-08          2 01-JAN-08
1     A     01-JAN-08          2 02-JAN-08
2     A     05-JAN-08          3 05-JAN-08
2     A     05-JAN-08          3 06-JAN-08
2     A     05-JAN-08          3 07-JAN-08
1     B     09-JAN-08          3 09-JAN-08
1     B     09-JAN-08          3 10-JAN-08
1     B     09-JAN-08          3 11-JAN-08

The above approach will work, but will consume an increasingly large amount of CPU time as the number of rows in the T4 table increases. To work around this issue, use an existing table in place of the FROM DUAL CONNECT BY logic - the existing table method was demonstrated by Andreas Mosmann's solution.

I would avoid using a stored procedure when a solution using only SQL is available.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Feb 13 2008 - 11:42:46 CST