can sum one help with this:
I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables
Thanks a lot.
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
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedWe have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedHello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies View RelatedI know this is not SQL forums but still if some body has solutions..then plz help
My backend is SQL.
My Ms Access database is always open and getting updated continously.
I need to create Job for SQL. This job will run every 10-15 min may be.
This job should pick latest data from MS Access and put in my SQL server database
Is it possible to create such kind of Job>
OR
Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!
Regards,
Mani
I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..
IS there a way? I need help.
Thanks
Hello to all!
I have a procedure "rebuild_index" and I would like to create a job running that procedure.
May someone send me a script (template) how to create this job, the most important:
When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script)
Thank you very much for your help!
create proc p_rebuild_index as
declare @name varchar(100),
@string varchar(200)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch c1 into @name
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)'
execute (@string)
--print @string
fetch c1 into @name
end
close c1
deallocate c1
GO
How can i create sdf files?
Is there any way to convert a mdb or a xml file to sdf?
The only way i was able to do this was with data port wizard from primeworks, but its not freeware...
Can i populate my sdf database from xml files by writing code...??
Does someone knows??
I have searched everywhere and didnt find anything...
I'm working in VS2005 vb.net and i have installed compact sql...
Thank you!
Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?
View 1 Replies View RelatedHi,
I am not sure is this a proper place for my question.
All we know that creating a database can be done as the following.
1) Create a project named DBExample
2) Click Add->New Item.
3) Select SQL Databse and type a name.(Ex: Database1.mdf) A database is added to the project
4) Then add table and input values.
5) Set the primary key....
My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
Thanks
Hi, What should I type in as the server name if I would want to create a local database, I tried 'local' and 'localhost' but it didn't work.
View 1 Replies View RelatedHello...
I want to develop a web site having two features
1. Online Shopping2. Forums
Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.
____________Thanks in advNauman Ahmed
I'm sorry to beat this to death but something odd is happening that I'm not quite sure I understand.
I have a number of DTS packages that when originally created with the wizard were scheduled and the jobs have run fine. Today, I created an import job that runs great but then crashes when I try to schedule it from the wizard. When I manually go in and try to schedule the DTS job, it accepts the input but does not create the job.
Since I've done this before, I'm confused about why I was able to do this previously, but not now. Permissions, etc. have not changed at all and I am the dbo for the database. The DTS package and attempted job scheduling are being done on Windows 2000 Server. SQL-Server version is 7.0.
Anybody have any ideas?
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
any clues?
i was asked to post this here..
_----------------------------------------
procedure creation
I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record
this is the cust table created
create table CUSTOMER (
CUSTOMER_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256));
I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...
any clues?
i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.
create table CUSTOMER
(
CUSTOMER_ID int identity(1000,1),
NAME VARCHAR2(45),
ADDRESS VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP_CODE VARCHAR2(9),
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
SALESPERSON_ID NUMBER(4),
CREDIT_LIMIT NUMBER(9,2),
COMMENTS VARCHAR2(256)
)
But while writing insert statement didnt specify this column.
Like
Insert into CUSTOMER (NAME ,ADDRESS ,CITY ,STATE ,ZIP_CODE ,AREA_CODE ,PHONE_NUMBER,SALESPERSON_ID , CREDIT_LIMIT ,COMMENTS )
values('xoxo','planet','earth','jupiter','1234','2 13',31231,231,1231,'1231')
not working.....!!?!?!
Hi
I am supposed to give scripts for an installtion at client site.
installing SQL server 2005 Developer edition
some databases already we have
tables
indexes
Stored procedures
jobs
Can u please guide me how to do this?
I am new to this.
Muralidaran r
I wanted to created a few jobs in my database.
How do i create it using the Batch files?????
Thanks,
Sandu_Bangalore
Hi all,
Here is the problem : I can not create a DTS package in SQLServer.
=> Error description : access denied
Environment :
- Windows NT4 sp6 Server, french, logon on as "Administrator".
- SQLServer 7.0 sp4, french, logon as "sa" on the NT Server with Enterprise Manager.
- SQLServer Agent runs under an Administrator account
Why can't i create à single DTS package ? Strange, isn't it ?
Many thanks for your ideas
Bertrand
Hi all,
Can I create one xml file through transact-sql ( in a stored procedure )?
From where i will get the tutorials ? Please help me ...
I am using the MSSQL Server 8.0 .
thanks
Pinto
We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .
View 6 Replies View RelatedI am a promotional DBA and need help with some code a developer wrote that errors out.
He is trying to create a role and to my knowledge there is nothing wrong with the syntax but it gives an incorrect syntax error.
CREATE ROLE 'PPS' IDENTIFIED BY 'stressboy';
error: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ROLE'.
we tried it with ' ' & "" and without either. I'm sure this is an easy one for you all. Thanks.
I want to create a form (Infopath) or web page where the user can request for a new database I want the user to fill in the required info (like the size, name columns.....) and ones they're done filling the required info, they will click on a button and that should trigger the database automation script to run and if the database with the requested name is not created, the script should create a database for the user.
View 1 Replies View RelatedHi,
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
Thanks,
Al
hi every body,
i have a problem in creating dsn ....i have sql server installed in
a system name RESEARCH.....when i wish to create a dsn to that server
from a system RESEARCH1 i'm unable to create it.......has anyone encountered such an error...plz help
thanks in advance
venkat
Life is Short Make it Sweet
dipak writes "can i create package (like oracle) in SQL server 2000? if yes how it possible plz help me."
View 1 Replies View RelatedHow to create a database in MS SQLserver 2005 express edition.
Pleae help me to get started.I have learned some SQL commands.
But i want to execute those in SQLserver 2005.But i don't know the procedure for creating a database.please help me on this.
Hi,
I'm just beginning to work with the Integration services package on a remote machine.When i try to open a new package, i receive the error message as
Failed to save package file "file path" with error 0x8002801D "Library not registered.".
How to handle this error?
Hi All,
Can anyone please explain how to schedule a backup job in SQL SERVER 2005.
Thanks in Advance
RKNAIR
Hi,
Currently i have one store procedure which is used to report generation. It creats some temporary tables ( # tables), and also uses while loop.
Now i wanted to create a view for the same functionality due to client requirment.
Now when i changed the code, it tells me that u cannot create a view with # tables. With the following Error :
"" Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables""
Any thoughts...
Thanks in advance.
-- Chetan