Home » SQL & PL/SQL » SQL & PL/SQL » Holiday flag in time dimension table
Holiday flag in time dimension table [message #215980] Wed, 24 January 2007 09:03 Go to next message
Dobson1954
Messages: 18
Registered: October 2006
Location: US
Junior Member
I have created a time dimension table in one of our datamart with several columns and populated data from 1995 to 2010. But as per the business requirement we need to add holiday flag in that table. As we are not aware of the future holidays, what is the best procedure to add holiday flag to this table. Can somebody send some sample PL/SQL code to update the holiday flag in time dimension table?

Any help on this is highly appreciated.

Thanks
Re: Holiday flag in a table [message #216176 is a reply to message #215980] Thu, 25 January 2007 07:50 Go to previous messageGo to next message
Dobson1954
Messages: 18
Registered: October 2006
Location: US
Junior Member
Guys,

Any help?

Thanks
Re: Holiday flag in time dimension table [message #216185 is a reply to message #215980] Thu, 25 January 2007 08:16 Go to previous messageGo to next message
vijaykasi
Messages: 11
Registered: January 2007
Location: London
Junior Member
You have to add a column Holiday Flag.

For multiple countries,add country_id as a foreign key to the time dimension table.
So,your time dimension would be something like-
Date_id Holiday_flag
12/25/2006 Y
12/26/2006 Y
12/27/2006 N
12/28/2006 N

There is no conventional source for this.

You need to create a spreadsheet and load the data into table.
Re: Holiday flag in time dimension table [message #216210 is a reply to message #215980] Thu, 25 January 2007 10:59 Go to previous messageGo to next message
Dobson1954
Messages: 18
Registered: October 2006
Location: US
Junior Member
Thanks for the reply.

How can i update the table with spreadsheet data once i added the holiday flag to the table. Using SQL* Loader??

Can you send me the sample code for that, if you have.

Thanks for your help.

Thanks
Re: Holiday flag in time dimension table [message #216215 is a reply to message #215980] Thu, 25 January 2007 11:27 Go to previous messageGo to next message
vijaykasi
Messages: 11
Registered: January 2007
Location: London
Junior Member
Yes, Using SQL LOADER

I beleive you have created a a table as below

CREATE TABLE HOLIDAY_TBL (
COUNTRY_ID NUMBER(2),
DATE_ID DATE,
HOLIDAY_FLAG VARCHAR2(1));

your data file sample should be like (test.dat)

1,25-NOV-2006,Y
1,26-NOV-2006,Y
1,27-NOV-2006,N
1,28-NOV-2006,N

country_id,date and holiday flag respectievly.

your control file (test.ctl)

LOAD DATA
INFILE 'c:\test.dat'
BADFILE 'c:\temp\test.bad'
DISCARDFILE 'c:\temp\test.dsc'
INSERT
INTO TABLE HOLIDAY_TBL (
COUNTRY_ID POSITION(1:2) INTEGER EXTERNAL,
DATE_ID POSITION(4:12) CHAR,
HOLIDAY_FLAG POSITION(14:15) INTEGER EXTERNAL)

(You have three options with Load type in control file INSERT,APPEND,REPLACE) Use accordingly..

Now you have control file,data file and you created table in database, so just need to run SQLLoader as below

sqlldr userid=userid/password control=c:\test.ctl log=c:\test.log

or run sqlldr from command prompt for more options.

Post your comments if you need more information or let me know your problem in detail.

Re: Holiday flag in time dimension table [message #216219 is a reply to message #216215] Thu, 25 January 2007 11:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be there is a difference in the terminology used here.
OP states:
>>How can i update the table with spreadsheet data once i added the holiday flag to the table. Using SQL* Loader??
SQL*LOADER cannot update.
Standard procedure is to insert into a staging table and use merge statements.
Or, I misread the thread.
Re: Holiday flag in time dimension table [message #216231 is a reply to message #215980] Thu, 25 January 2007 12:55 Go to previous messageGo to next message
Dobson1954
Messages: 18
Registered: October 2006
Location: US
Junior Member
Thanks for your inputs.

Here is what i have done and what i want.

I have created the table and popluated the data using the below scripts.


====================================
-- Create Time Dimension Table
CREATE TABLE TIME_DIM
(
DAY_ID DATE PRIMARY KEY,
DAY_DEC VARCHAR2(20),
DAY_NAME VARCHAR2(10),
DAY_OF_WEEK VARCHAR2(1),
DAY_OF_MONTH VARCHAR2(2),
DAY_OF_YEAR VARCHAR2(3),
DAY_IN_DAY VARCHAR2(1),
MONTH_ID VARCHAR2(10),
MONTH_DESC VARCHAR2(15),
MONTH_OF_QUARTER VARCHAR2(2),
MONTH_OF_YEAR VARCHAR2(2),
MONTH_NAME VARCHAR2(10),
END_OF_MONTH DATE,
DAYS_IN_MONTH VARCHAR2(2),
QUARTER_ID VARCHAR2(10),
QUARTER_DESC VARCHAR2(20),
QUARTER_OF_YEAR VARCHAR2(1),
END_OF_QUARTER DATE,
DAYS_IN_QUARTER VARCHAR2(2),
YEAR_ID VARCHAR2(4),
DAYS_IN_YEAR VARCHAR2(3),
END_OF_YEAR DATE,
DAYS_IN_WEEK VARCHAR2(1),
CAL_WEEK_ID VARCHAR2(10),
CAL_WEEK_DESC VARCHAR2(45),
CAL_WEEK_OF_YEAR VARCHAR2(2),
END_OF_CAL_WEEK DATE
)
/

-- Populate the data since 1994 to till 2010

Insert into TIME_DIM
SELECT
time_id,
INITCAP(TO_CHAR(time_id,'fmMonth DD, YYYY')),
INITCAP(TO_CHAR(time_id, 'fmDAY')),
TO_NUMBER(TO_CHAR(time_id - 1, 'D')),
TO_NUMBER(TO_CHAR(time_id, 'DD')),
TO_NUMBER(TO_CHAR(time_id, 'DDD')),
1,
TO_CHAR(time_id, 'YYYY"-M"MM'),
TO_CHAR(time_id, 'fmMonth YYYY'),
DECODE(MOD(TO_NUMBER(TO_CHAR(time_id, 'MM')), 4), 0, 4, MOD(TO_NUMBER(TO_CHAR(time_id, 'MM')), 4)),
TO_NUMBER(TO_CHAR(time_id, 'MM')),
TO_CHAR(time_id, 'fmMonth'),
LAST_DAY(time_id),
TO_CHAR(LAST_DAY(time_id),'DD'),
TO_CHAR(time_id, 'YYYY"-Q"Q'),
INITCAP(TO_CHAR(time_id, 'fmQth "quarter," YYYY')),
TO_NUMBER(TO_CHAR(time_id, 'Q')),
TRUNC(ADD_MONTHS(time_id,3), 'Q') - 1,
(TRUNC(ADD_MONTHS(time_id,3), 'Q') - 1) - (TRUNC(time_id, 'Q') - 1),
TO_NUMBER(TO_CHAR(time_id, 'YYYY')),
(TRUNC(ADD_MONTHS(time_id,12), 'YYYY') - 1) - (TRUNC(time_id, 'YYYY') - 1),
TRUNC(ADD_MONTHS(time_id,12), 'YYYY') - 1,
7,
TO_CHAR(time_id, 'IYYY') || '-CW' || TO_CHAR(time_id, 'IW'),
INITCAP(TO_CHAR(time_id, 'fmIWth "week of" IYYY')) || ', ending ' || TO_CHAR(TRUNC(time_id + 7, 'IW') - 1, 'fmMonth DD, YYYY'),
TO_NUMBER(TO_CHAR(time_id, 'IW')),
TRUNC(time_id + 7, 'IW') - 1
FROM (SELECT to_date('01/01/1994','MM/DD/YYYY') + rownum - 1 AS time_id
FROM all_objects
WHERE rownum <= 6209)
ORDER BY time_id
/

=========================================

Now i want to add two columns to the table called "Holiday Flag", Holiday Description". I want to update this table at the begining of each year as the holiday list is available for that calender year. What is the best procedure to achieve this.

Appreciate your help.

Thanks.
Re: Holiday flag in time dimension table [message #216244 is a reply to message #216231] Thu, 25 January 2007 13:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This needs some sql skill that certainly i am in lack of. Smile
I would just wait for the other elites to respond.
Re: Holiday flag in time dimension table [message #216257 is a reply to message #216244] Thu, 25 January 2007 15:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I see it, you could already have added those columns in CREATE TABLE statement. As you did not, you may add them now using
ALTER TABLE time_dim ADD holiday_flag VARCHAR2(1);
ALTER TABLE time_dim ADD holiday_description VARCHAR2(50);


INSERT INTO should have a column list (i.e. specify all columns you are inserting into; those that are NULL omit from the list, or - even better - specify the fact they are to be NULL). Something like this:
INSERT INTO time_dim 
  (date_id, 
   day_dec, 
   day_name, ...
   holiday_flag,
   holiday_description
  )
SELECT 
   time_id,
   INITCAP(TO_CHAR(time_id,'fmMonth DD, YYYY')),
   INITCAP(TO_CHAR(time_id, 'fmDAY')), ...
   NULL,
   NULL
FROM ...;

How to populate 'holiday' columns? You could use a few options. First one is the simplest - write several UPDATE statements which would
UPDATE time_dim SET
  holiday_flag = 'Y',
  holiday_description = 'New Year'
WHERE date_id = TO_DATE('01.01.2007', 'dd.mm.yyyy');
and do the same for all holidays in a year.

Another option is to have a list of holidays in a textual file
01.01.2007;New Year
04.07.2007;4th of July
...
Use this file as an external table and directly update 'time_dim' table:
UPDATE time_dim t SET
  (holiday_flag, holiday_description) = (SELECT 'Y', e.holiday_description
                                         FROM external_table e
                                         WHERE e.holiday_date = t.date_id)
  WHERE EXISTS (SELECT NULL FROM external_table e
                WHERE e.holiday_date = t.date_id);

Or, if you can not use external tables, load this file into a temporary table (using SQL*Loader) and apply the same UPDATE statement as in a previous example.

I hope I didn't miss the point.
Re: Holiday flag in time dimension table [message #216316 is a reply to message #216257] Fri, 26 January 2007 06:43 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
If it's truly a one-off update, I would be tempted to load the holiday data by just copying & pasting it from Excel into PL/SQL Developer or whatever you use. Obviously an External Table is a more robust solution in the long term though.
Re: Holiday flag in time dimension table [message #216807 is a reply to message #215980] Tue, 30 January 2007 09:02 Go to previous messageGo to next message
Dobson1954
Messages: 18
Registered: October 2006
Location: US
Junior Member
Thanks for your inputs.

We decided to go with update statements.

Thanks again for all your help.
Re: Holiday flag in time dimension table [message #216810 is a reply to message #215980] Tue, 30 January 2007 09:43 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I have one question, why are you storing all the time information, when all you need is a single date column and ALL the other information would be simply generated using function calls.
Previous Topic: READ FILE IN LOCAL
Next Topic: Table as Input Parameter (merged)
Goto Forum:
  


Current Time: Sat Dec 10 09:01:59 CST 2016

Total time taken to generate the page: 0.10371 seconds