Creation of Material Views. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creation of Material Views.

Author

Topic

cvbkraju
Starting Member

2 Posts

Posted - 2008-02-13 : 13:41:56

Hi,
I would like to create views in TOAD 9.1 Application.
Could any one help me how to write Sql.

Below are the tables which i have created.
1) DIM_RANK_GRP:

CREATE TABLE DIM_RANK_GRP
(RANK_GRP_ID NUMBER NOT NULL,
RANK_GRP VARCHAR (20),
PARENT_RANK_GRP VARCHAR (20)
HIERARCHY_TYPE VARCHAR (20)
);

ALTER TABLE DIM_RANK_GRP
ADD PRIMARY KEY (RANK_GRP_ID)

2) DIM_PROFITABILITY:

CREATE TABLE DIM_PROFITABILITY
(
PROFIT_ID NUMBER NOT NULL,
PROFIT_VAL NUMBER,
PROFIT_DESC VARCHAR (30)
);

ALTER TABLE DIM_PROFITABILITY
ADD PRIMARY_KEY (PROFIT_ID)

3) Fact Table: Agg_Rx_Weekly_Sales:

CREATE TABLE AGG_RX_WKLY_SLS
(TERR_ID Number,PFZ_PROD_ID Number ,DT_SK Date,CHNL_ID Number,SPEC_ID Number,RANK_GRP_ID Number,PRFTY_ID Number,Nrx Float(64),Trx Float(64),
Nrx_unit Float(64),Trx_unit Float(64),Nrx_Dol Float(64),Trx_Dol Float(64),Mkt_Nrx_Unit Float(64),Mkt_Trx_Unit Float(64),Mkt_Nrx_Dol Float(64),Mkt_Trx_Dol Float(64),Mkt_Nrx Float(64),Mkt_Trx Float(64)
);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_TERR
FOREIGN KEY (“TERR_ID”)
REFERENCES “DIM_TERR” (“TERR_ID”);

ALTER TABLE”AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_PERD
FOREIGN KEY (“DT_SK”)
REFERENCES “DIM_PERD” (“DT_SK”);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_SPEC
FOREIGN KEY (“SPEC_ID”)
REFERENCES “DIM_SPEC” (“SPEC_ID”);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_CHNL
FOREIGN KEY (“CHNL_ID”)
REFERENCES “DIM_CHNL” (“CHNL_ID”);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_REL_PROD_MKT
FOREIGN KEY (“PFZ_PROD_ID”)
REFERENCES “REL_PROD_MKT” (PFZ_PROD_ID”);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_RANK_GRP
FOREIGN KEY (“RANK_GRP_ID”)
REFERENCES “DIM_RANK_GRP”(RANK_GRP_ID”);

ALTER TABLE “AGG_RX_WKLY_SLS”
ADD CONSTRAINT FK_DIM_PRFTY
FOREIGN KEY (“PRFTY_ID”)
REFERENCES “DIM_PRFTY” (PRFTY_ID”)

Advanced Thanks.

VENKAT

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 14:33:24

What data are you trying to retrieve?

CoolEJ
Starting Member

7 Posts

Posted - 2008-02-13 : 16:30:35

You'll be using Create View... and identify the data you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources