January 6, 2012 at 3:42 pm
hi
I am really struggling here can somebody please help!
I have two tables:
create table test1 (t1id number (pk), t1name varchar2(5), t1job varchar2(20));
insert into test1 values (10, 'Paul', 'Driver');
insert into test1 values (11, smith', 'Chef');
insert into test1 values (13, 'andy', 'police');
create table test2 (t2id number (pk) t2name varchar2(5), t2job varchar2 (20))
insert into test1 values (20, 'john ','teacher ');
insert into test1 values (21, Peter', 'Manager');
insert into test1 values (33, 'major', 'Boxer');
can anyone help me populate the fact table with the appropriate query (procedure). I am aware of the fact the the fact table needs to have the foreign keys fromthe the dimensioanl tables. I need the procedure.
the values in the tables does not matter by the way
please help
New kid on the block
January 6, 2012 at 5:07 pm
Insert the key into the fact table from your dimension table joined to the incoming data.
January 7, 2012 at 2:17 am
Where’s the Fact here? Table1 & Table2 look like Dimension table only.
January 7, 2012 at 3:24 am
create table test1 (t1id number (pk), t1name varchar2(5), t1job varchar2(20));
insert into test1 values (10, 'Paul', 'Driver');
insert into test1 values (11, smith', 'Chef');
insert into test1 values (13, 'andy', 'police');
create table test2 (t2id number (pk) t2name varchar2(5), t2job varchar2 (20))
insert into test1 values (20, 'john ','teacher ');
insert into test1 values (21, Peter', 'Manager');
insert into test1 values (33, 'major', 'Boxer');
create fact_table (t1id number, t2id number);
New kid on the block
January 7, 2012 at 3:48 am
Where are you stuck in Fact Population? Please post your script here, we will debug it if it fails or need improvements.
Also, it’s a Fact-Less-Fact table. Is it the intended design or you came up with something because I asked a question?
January 7, 2012 at 4:01 am
no the table name is actually called "fact_table". the idea is to insert td1id and td2id into the "fact_table.
New kid on the block
January 7, 2012 at 4:06 am
Delario (1/7/2012)
no the table name is actually called "fact_table". the idea is to insert td1id and td2id into the "fact_table.
What about posting your scripts here? Did you try anything so far?
January 7, 2012 at 4:27 am
well this morning I am trying on pretty similar thing but with no success. take a look at this
CREATE TABLE COURSE
(
CID NUMBER primary key NOT NULL ENABLE,
DETAILS VARCHAR2(20 BYTE),
CAMPUS VARCHAR2(20 BYTE),
STID NUMBER);
---insert statement was used here, so do not worry---
select * from course
CID DETAILS CAMPUS STID
------ -------------------- ----------- ------------
8989 hnd king 666
5656 cms william 333
7788 science barking 888
4455 history james 888
CREATE TABLE STUDENT
(
STID NUMBER primary key NOT NULL ENABLE,
NAME VARCHAR2(20 BYTE),
DOB DATE);
---insert statement was used here, so do not worry---
select * from student
STID NAME DOB
------- -------------------- --------------
666 john mith 09-JUN-89
333 lokmi nhuj 07-NOV-99
888 miolk hyhy 07-JUN-99
CREATE TABLE "MD833"."FACT_COURSE"
(
STID NUMBER,
CID NUMBER,
CONSTRAINT "FAC" FOREIGN KEY ("CID") REFERENCES COURSE (CID) ENABLE,
CONSTRAINT "SED" FOREIGN KEY ("STID") REFERENCES STUDENT (STID) ENABLE
);
doing that, I am using this script:
declare cursor test_fact is
select s.stid , d.cid
from student s, course d
where s.stid = d.stid
and d.stid = s.stid;
begin
for s_rec in test_fact loop
insert into fact_course values
(
s_rec.s, s_rec.d
);
end loop;
end;
------error message-------
error report:
ORA-06550: line 10, column 16:
PLS-00302: component 'D' must be declared
ORA-06550: line 10, column 16:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:%s"
*Cause: Usually a PL/SQL compilation error.
*Action
-----------------------------------------
where do I go from here I am really stuck
New kid on the block
January 7, 2012 at 4:40 am
You are repeating your mistake. It’s SQL Server forum. You shouldn’t expect help on Oracle queries here because the syntaxes & data types differ in these two technologies.
Even if somebody tries to give you a code (SQL Server) it won’t work on your Oracle Server. Please post your questions (code related) to Oracle forums. We can help you on conceptual questions, if required.
January 7, 2012 at 4:41 am
ok Dev thanks any way
New kid on the block
January 7, 2012 at 4:47 am
Thank all of you, I am just a new kid on the block
New kid on the block
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply