When I run the select statement at the end, I'm receiving the "no selected statement" message even though I selected some variables.
Also I am having trouble inputting data into the FACT Table
REM Drop table
REM ============================================
REM ADD the DROP TABLES After Here
REM ============================================
Drop table Dummy cascade constraint;
Drop table Budget_Fact cascade constraint;
Drop table Dept_Dim cascade constraint;
Drop table Time_Dim cascade constraint;
REM ============================================
REM ADD the DROP SEQUENCE After Here
REM ============================================
drop sequence Deptidseq;
drop sequence timeidseq;
REM DO NOT CHANGE!!!!!
REM ============================================
CREATE TABLE DUMMY(DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
REM ============================================
REm Create Tables
REM ============================================
REM ADD the CREATE TABLE STATEMENT for
REM all Dim and Fact Tables After Here!!!!
REM ============================================
Create table time_dim (
timeid number(8),
hireyear Date,
Constraint timeid_pk Primary Key (timeid));
Create Table dept_dim (
deptid number (8),
deptno number (6),
dname varchar2(14),
Constraint deptid_pk Primary Key (deptid));
Create Table Budget_Fact(
timeid number(8),
deptid number(8),
totalbudget Number (10,2),
constraint timeid_fk Foreign Key (timeid) references time_dim(timeid),
constraint deptid_fk Foreign Key (deptid) references dept_dim(deptid));
REM ============================================
REM Add the SEQUENCE Code here for each
REM Surrogate Key(s).
REM ============================================
create sequence deptidseq;
create sequence timeidseq;
REM ============================================
REM Add the Trigger Code for each Dim table.
REM ============================================
CREATE OR REPLACE TRIGGER departmenttrig
BEFORE INSERT ON dept_dim
FOR EACH ROW
BEGIN
SELECT Deptidseq.NextVal INTO :New.Deptid
FROM Dummy;
END;
/
Create OR Replace Trigger timeidtrig
Before Insert ON Time_dim
For each row
Begin
Select timeidseq.nextval Into :New.timeid
From dummy;
End;
/
REM ============================================
REM Add the Function Code for each Dim table.
REM ============================================
CREATE OR REPLACE FUNCTION GetTimeId (in_YY IN VarChar2)
Return Number
IS
Timeid_Num Number(12);
TmpIn_Date VARCHAR2(12);
BEGIN
TmpIn_Date := '01-JAN-' || in_YY;
SELECT TimeId INTO TimeId_Num
From Time_Dim
WHERE to_date(HireYear, 'DD-MON-YY') = to_date(TmpIn_Date,'DD-MON-YY');
RETURN TimeId_Num;
End;
/
Create or Replace Function GetDeptID (inDeptNo IN varchar2)
Return Number
Is
DeptID_num number(8);
Begin
Select Deptid into DeptID_num
From Dept_Dim
where Deptno = inDeptNo;
Return DeptID_num;
End;
/
Create or Replace Function GetTotalBudget(in
REM INSERT . . . SELECT
REM ============================================
REM Add the INSERT . . . SELECT Code here.
REM ============================================
Insert Into Time_Dim (hireyear)
Select Distinct hiredate
From emp;
Insert Into Dept_Dim (deptno,dname)
Select deptno, dname
From dept;
Insert Into Budget_Fact (Timeid, Deptid, TotalBudget)
Select GetTimeID(To_Char(HireDate,'YYYY')),GetDeptID(DeptNo), Sum(Sal)
From EMP
Group By To_Char(HireDate,'YYYY'), DeptNo;
REM Formatting Statements
REM ============================================
column HireYear format a10
column Department format a15
column TotalBudget format $9,999,999.99
REM ============================================
REM SELECT
REM ============================================
REM Add SELECT with ROLLUP from Data Warehouse
REM ============================================
Select D.DeptID ||'' ||D.DName As Department, HireYear, TotalBudget
From Time_Dim T, Dept_Dim D, Budget_Fact B
Where (B.Timeid= T.Timeid) and (B.DeptId= D.DeptId);
select * from Budget_Fact;
REM SELECT
REM ============================================
REM Add SELECT with ROLLUP from Source Databased
REM ============================================
Select
From
Where
Group By Rollup