Home » SQL & PL/SQL » SQL & PL/SQL » Help to Create this table with the following structure (10.2.0.1.0)
Help to Create this table with the following structure [message #560719] Mon, 16 July 2012 09:09 Go to next message
suryac8
Messages: 6
Registered: December 2009
Junior Member
create table fct_sales ( prd_id varchar2(10), region_id varchar2(10),sale_date date, rev number(6));

insert into fct_sales (1,'TX',to_date(01/01/2012,'dd/mm/yyyy'),50)
insert into fct_sales (1,'TX',to_date(11/01/2012,'dd/mm/yyyy'),5)
insert into fct_sales (1,'TX',to_date(21/01/2012,'dd/mm/yyyy'),60)
insert into fct_sales (1,'TX',to_date(01/02/2012,'dd/mm/yyyy'),90)
insert into fct_sales (1,'TX',to_date(14/02/2012,'dd/mm/yyyy'),110)
insert into fct_sales (1,'TX',to_date(15/02/2012,'dd/mm/yyyy'),5)
insert into fct_sales (5,'TX',to_date(05/03/2012,'dd/mm/yyyy'),57)
insert into fct_sales (5,'TX',to_date(07/03/2012,'dd/mm/yyyy'),5)
insert into fct_sales (2,'TX',to_date(01/04/2012,'dd/mm/yyyy'),62)
insert into fct_sales (2,'TX',to_date(11/04/2012,'dd/mm/yyyy'),11)
insert into fct_sales (4,'TX',to_date(21/04/2012,'dd/mm/yyyy'),21)
insert into fct_sales (4,'TX',to_date(22/04/2012,'dd/mm/yyyy'),22)
insert into fct_sales (4,'TX',to_date(11/05/2012,'dd/mm/yyyy'),23)
insert into fct_sales (3,'TX',to_date(17/05/2012,'dd/mm/yyyy'),24)
insert into fct_sales (3,'TX',to_date(18/05/2012,'dd/mm/yyyy'),45)
insert into fct_sales (3,'TX',to_date(20/05/2012,'dd/mm/yyyy'),21)
insert into fct_sales (4,'TX',to_date(01/06/2012,'dd/mm/yyyy'),22)
insert into fct_sales (3,'TX',to_date(01/06/2012,'dd/mm/yyyy'),10)
insert into fct_sales (2,'TX',to_date(01/07/2012,'dd/mm/yyyy'),47)
insert into fct_sales (2,'TX',to_date(11/07/2012,'dd/mm/yyyy'),50)

insert into fct_sales (4,'TX',to_date(21/07/2012,'dd/mm/yyyy'),50)
insert into fct_sales (3,'TX',to_date(22/07/2012,'dd/mm/yyyy'),50)

insert into fct_sales (3,'TX',to_date(23/07/2012,'dd/mm/yyyy'),50)



this data is at day level

i have to create same table but which stores data at Fiscal weeklevel which means
the data at each row should be like

insert into fct_sales (1,'TX',2012-W01,50)

where ever the data can be group by week we should have it aggregated ie if you have two records with
same prd_id,region_id, and sales date is different but falls in same fiscal week then that record's revenue should
be aggregated grouped by all the prd_id,region_id,

insert into fct_sales (5,'TX',to_date(05/03/2012,'dd/mm/yyyy'),57)
insert into fct_sales (5,'TX',to_date(07/03/2012,'dd/mm/yyyy'),5)

THE ABOVE TWO ROWS SHOULD BECOME
insert into fct_sales (5,'TX','2012-W10',62)

I need suggestion on create table statement. what should it be like? specialy the week level storage
and how do i accomplish thisusing create table XXX as select ....from table

Appreciate your suggestions.

Re: Help to Create this table with the following structure [message #560724 is a reply to message #560719] Mon, 16 July 2012 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
It is ALWAYS bad plan to store computed data in static table.
Consider just CREATE VIEW instead

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: Help to Create this table with the following structure [message #560738 is a reply to message #560719] Mon, 16 July 2012 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A detail description of why you must not do that is in the following topic:
http://www.orafaq.com/forum/m/360742/102589/?msg_360742#msg_360742

Regards
Michel
Re: Help to Create this table with the following structure [message #561011 is a reply to message #560719] Wed, 18 July 2012 04:51 Go to previous messageGo to next message
suryac8
Messages: 6
Registered: December 2009
Junior Member
Thanks Michel,
This is a summary table built on Transaction table .
This table is used only to create report using Cognos.
Suppose I have to create a table where I am storing data at week level how to define the datatype for this column
They are planning to load data into that table using Informatica.
Re: Help to Create this table with the following structure [message #561015 is a reply to message #561011] Wed, 18 July 2012 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Suppose I have to create a table where I am storing data at week level how to define the datatype for this column


Of which column? The one mentioning the week? It depends on the query you'll have to do but most likely a DATE column is the best choice.

Regards
Michel
Re: Help to Create this table with the following structure [message #561021 is a reply to message #561015] Wed, 18 July 2012 05:44 Go to previous messageGo to next message
suryac8
Messages: 6
Registered: December 2009
Junior Member
Thanks but im storing data for week as 2011-W01.
So varchar is the appropriate Correct?
Re: Help to Create this table with the following structure [message #561023 is a reply to message #561021] Wed, 18 July 2012 06:02 Go to previous messageGo to next message
John Watson
Messages: 4572
Registered: January 2010
Location: Global Village
Senior Member
First, your test case doesn;t work because the INSERTS are wrong:
orcl> insert into fct_sales (1,'TX',to_date(01/01/2012,'dd/mm/yyyy'),50);
insert into fct_sales (1,'TX',to_date(01/01/2012,'dd/mm/yyyy'),50)
                       *
ERROR at line 1:
ORA-00928: missing SELECT keyword


orcl>

I think you can extract your week number by using the format models IW or WW.
From the docs:

IW
Week of year (1-52 or 1-53) based on the ISO standard.

WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
orcl> select to_char(sysdate,'iw') from dual;

TO
--
29

You can use this for display, and also for the aggregation.


Re: Help to Create this table with the following structure [message #561024 is a reply to message #561021] Wed, 18 July 2012 06:05 Go to previous message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not really, nothing prevents you from storing 2001-W99, you must have some check constraint.
However, as it is an aggregate table (not an OLTP one) which is only loaded by one program and queried by others, this point is not so important.
Another point is that it does not say which kind of week it is (legal, ISO...).
A last one (and maybe the most important for your application), if you have to make some computation on dates, you'll have to convert this string into a real date.

Regards
Michel
Previous Topic: Rownum
Next Topic: Remote Procedure
Goto Forum:
  


Current Time: Mon Sep 22 03:53:29 CDT 2014

Total time taken to generate the page: 0.14849 seconds