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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f3411d27339e4c9354295e5c6e2a9fad?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f3411d27339e4c9354295e5c6e2a9fad?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f3411d27339e4c9354295e5c6e2a9fad?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 #652769 is a reply to message #652767] |
Sat, 18 June 2016 14:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 23:43:18 CDT 2024
|