Re: SQL Help

NN schrieb am 13.02.2008 in
<9754b4a7-a07a-4d69-ba90-e555dbae4612_at_v46g2000hsv.googlegroups.com>:

> 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.
Quick and dirty, something like

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