 | |
#11
 
| |||
| |||
|
Re: SQL Help -
02-13-2008
, 11:42 AM
|
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. |
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.
#12
| |||
| |||
|
|
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. |
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.
#13
| |||
| |||
|
|
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. |
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.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.