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  |
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 #561011 is a reply to message #560719] |
Wed, 18 July 2012 04:51  |
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 #561023 is a reply to message #561021] |
Wed, 18 July 2012 06:02 |
John Watson
Messages: 3102
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 |
 |
Michel Cadot
Messages: 54126
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
|
|
|
|
Goto Forum:
Current Time: Sat May 18 19:17:43 CDT 2013
Total time taken to generate the page: 0.13148 seconds
|
] 
Members
Search
Help
Register
Login
Home
