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.

NN schrieb am 13.02.2008 in
<9754b4a7-a07a-4d69-ba90-e555dbae4612 (AT) v46g2000hsv (DOT) googlegroups.com>:


select
Product,
Store,
Day,
Promotion_Days,
Day+X.MyNumber Promotion_Valid_On
from
MyPromoTable
join
(
select
rownum - 1 MyNumber
from
dictionary
where
rownum<1000
) x
on
X.MyNumber<Promotion_Days
order by
1,2,3,4,5

But I think it would be better to write a stored procedure

Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

NN schrieb am 13.02.2008 in
<9754b4a7-a07a-4d69-ba90-e555dbae4612 (AT) v46g2000hsv (DOT) googlegroups.com>:


select
Product,
Store,
Day,
Promotion_Days,
Day+X.MyNumber Promotion_Valid_On
from
MyPromoTable
join
(
select
rownum - 1 MyNumber
from
dictionary
where
rownum<1000
) x
on
X.MyNumber<Promotion_Days
order by
1,2,3,4,5

But I think it would be better to write a stored procedure

Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

NN schrieb am 13.02.2008 in
<9754b4a7-a07a-4d69-ba90-e555dbae4612 (AT) v46g2000hsv (DOT) googlegroups.com>:


select
Product,
Store,
Day,
Promotion_Days,
Day+X.MyNumber Promotion_Valid_On
from
MyPromoTable
join
(
select
rownum - 1 MyNumber
from
dictionary
where
rownum<1000
) x
on
X.MyNumber<Promotion_Days
order by
1,2,3,4,5

But I think it would be better to write a stored procedure

Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

NN schrieb am 13.02.2008 in
<9754b4a7-a07a-4d69-ba90-e555dbae4612 (AT) v46g2000hsv (DOT) googlegroups.com>:


select
Product,
Store,
Day,
Promotion_Days,
Day+X.MyNumber Promotion_Valid_On
from
MyPromoTable
join
(
select
rownum - 1 MyNumber
from
dictionary
where
rownum<1000
) x
on
X.MyNumber<Promotion_Days
order by
1,2,3,4,5

But I think it would be better to write a stored procedure

Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

That Works. Thanks.

On Feb 13, 11:10*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

That Works. Thanks.

On Feb 13, 11:10*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

That Works. Thanks.

On Feb 13, 11:10*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

That Works. Thanks.

On Feb 13, 11:10*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

On Feb 13, 10:41*am, NN <nav.n... (AT) gmail (DOT) com> wrote:
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.