Home » Developer & Programmer » Data Integration » Load Data into fact table
Load Data into fact table [message #652745] Fri, 17 June 2016 11:04 Go to next message
charlie2016
Messages: 4
Registered: June 2016
Junior Member
Hi,

Am trying to load data into the sales fact table however i get the error below after running the query

insert into sales
(market_Id,product_Id,time_Id,sales_Amt)
values (1,1002,(SELECT time_Id from time where year = 1997),2500);

SQL Error: ORA-02291: integrity constraint (HR.SYS_C0014611) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

Below are the tables and the sql queries:

CREATE TABLE  MARKET (
market_Id    NUMBER PRIMARY KEY,
city        VARCHAR2(15),
state       VARCHAR2(3),
region      CHAR(1)
);

insert into market values (1,'Melb','Vic','A');
insert into market values (2,'Adelaide','SA','A');
insert into market values (3,'Brisbane','Qld','A');
insert into market values (4,'Perth','WA','A');

CREATE TABLE  PRODUCT (
product_Id    NUMBER PRIMARY KEY,
name         VARCHAR2(20),
category     VARCHAR2(15),
price        NUMBER(5,2)
);

insert into product values (1001,'Pens','Stationary',5.00);
insert into product values (1002,'Pencil','Stationary',2.00);
insert into product values (1003,'Book','Stationary',6.00);
insert into product values (1004,'Ink','Stationary',3.50);
select * from product;

CREATE TABLE  TIME (
time_Id      DATE PRIMARY KEY,
week        NUMBER(2),
month       CHAR(3),
quarter     NUMBER(1),
year	        NUMBER(4)
);

insert into time values (sysdate,1,'MAR',3,1993);
insert into time values (sysdate,2,'SEP',4,1995);
insert into time values (sysdate,3,'SEP',4,1995);
insert into time values ('15-OCT-2000',1,'NOV',2,1997);
insert into time values ('16-DEC-2012',3,'OCT',3,1996);
select * from time;
update time set TIME_ID='6-JUL-2012' where year=1995 and week=2;

CREATE TABLE SALES (
market_Id      NUMBER  REFERENCES MARKET,
product_Id     NUMBER  REFERENCES PRODUCT,
time_Id        DATE    REFERENCES TIME,
sales_Amt     NUMBER(7,2),
PRIMARY KEY (market_Id,product_Id,time_Id)
);

insert into sales 
(market_Id,product_Id,time_Id,sales_Amt)
values (1,1002,(SELECT time_Id from time where year = 1997),2500);

Please advise.
Re: Load Data into fact table [message #652746 is a reply to message #652745] Fri, 17 June 2016 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Almost perfect post.
Please read How to use [code] tags and make your code easier to read.

Also, your test case is quite weird.
First TIME primary key is first column TIME_ID, the first 3 statement INSERT INTO TIME use SYSDATE as the first column value, so you will have a primary key violation if I copy and paste to execute it as the 3 INSERTs will be in the same second. In addition, given the 2 subsequent INSERT, I think you want only the DATE part of SYSDATE.
Is this just a test case mistake or a general error in your actual case?

Note: '15-OCT-2000' is not a date but a string, ALWAYS use TO_DATE with the proper format to insert date values (as not all people use the same default format mask). And it is advisable to use month number and not month name as not all people use the same language:
SQL> select to_date('15-OCT-2000') from dual;
select to_date('15-OCT-2000') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_date('15-OCT-2000','DD-MON-YYYY') from dual;
select to_date('15-OCT-2000','DD-MON-YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month
Re: Load Data into fact table [message #652758 is a reply to message #652745] Sat, 18 June 2016 02:11 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Your test case works for me, though the dates are weird because of your assumptions about nls_date_format. This is the insert into SALES:
orclz> insert into sales
  2  (market_Id,product_Id,time_Id,sales_Amt)
  3  values (1,1002,(SELECT time_Id from time where year = 1997),2500);

1 row created.

orclz> select * from time;

TIME_ID                   WEEK MON    QUARTER       YEAR
------------------- ---------- --- ---------- ----------
2016-06-18:08:07:04          1 MAR          3       1993
0006-07-20:12:00:00          2 SEP          4       1995
0015-10-20:00:00:00          1 NOV          2       1997
0016-12-20:12:00:00          3 OCT          3       1996

orclz>
Re: Load Data into fact table [message #652764 is a reply to message #652758] Sat, 18 June 2016 09:46 Go to previous messageGo to next message
charlie2016
Messages: 4
Registered: June 2016
Junior Member
The time table below shows the following data:

"TIME_ID" "WEEK" "MONTH" "QUARTER" "YEAR"
04-MAR-91 1 "MAR" 3 1993
06-JUL-12 2 "SEP" 4 1995
17-JUN-16 3 "SEP" 4 1995
15-OCT-00 1 "NOV" 2 1997
16-DEC-12 3 "OCT" 3 1996

Even if I try to insert the date in the format DD-MM-YYYY (eg:06-07-2012) it displays the date in the format DD-MMM-YYYY after inserting the record.

The sysdate function used in the above post was for test purposes. The date field is unique as you can see the data in the table above. Am not able to populate the Sales fact table with the date from the time table.

John, you were successful when running the query below, however, i still do get the error:

insert into sales
(market_Id,product_Id,time_Id,sales_Amt)
values (1,1002,(SELECT time_Id from time where year = 1997),2500);

Error report -
SQL Error: ORA-02291: integrity constraint (HR.SYS_C0014611) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

Am not sure why it says parent key not found when clearly there is data in the table.
Re: Load Data into fact table [message #652765 is a reply to message #652764] Sat, 18 June 2016 09:48 Go to previous messageGo to next message
charlie2016
Messages: 4
Registered: June 2016
Junior Member
Is it because its storing the date as string. How can I check this? When inserting the date into the time table am using the following sql:

insert into time values ('04-MAR-1991',1,'MAR',3,1993);
insert into time values ('06-JUL-2012',2,'SEP',4,1995);
insert into time values ('17-JUN-2016',3,'SEP',4,1995);
insert into time values ('15-OCT-2000',1,'NOV',2,1997);
insert into time values ('16-DEC-2012',3,'OCT',3,1996);
Re: Load Data into fact table [message #652766 is a reply to message #652765] Sat, 18 June 2016 09:50 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
You have already been told that you need to use the TO_DATE function to convert your strings to dates. You have also been asked to use [code] tags to format your posts correctly.
Re: Load Data into fact table [message #652767 is a reply to message #652766] Sat, 18 June 2016 10:47 Go to previous messageGo to next message
charlie2016
Messages: 4
Registered: June 2016
Junior Member
Thanks for your prompt reply.

This post is now closed.

Used the below sql to insert dates into the time table;

insert into time values (to_date('04-03-1991','DD-MM-YYYY'),1,'MAR',3,1993);

Then successfully ran the insert statement for the sales fact table.

[Updated on: Sat, 18 June 2016 10:59]

Report message to a moderator

Re: Load Data into fact table [message #652768 is a reply to message #652767] Sat, 18 June 2016 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

That's correct, now post INSERT statements for rows in order to get the error you mentioned in your first post.

Re: Load Data into fact table [message #652769 is a reply to message #652767] Sat, 18 June 2016 14:20 Go to previous message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Received in a mail to my work address::Quote:
Hi John,

Thank for your post regarding loading data into fact table, however, am still having an issue with the sql. you were successfull in creating the record for the fact table. could you please pass me the sql for inserting data into the time table. Think the way i'm entering the date into the time table is the issue.

Please advise.
If you would like assistance privately, you will need to contact my boss to sort out the appropriate charge rate.
Previous Topic: Schedule job sequentially
Next Topic: ODI Opetator Log
Goto Forum:
  


Current Time: Tue Mar 19 04:48:36 CDT 2024