|
Still being fairly new to SQL I let SSMS write alot of the script for me when I can.
The problems is I have not had a chance to dive into the create table stuff and other aspects of SQL, but I need to get the following code down into a single Create Table Command rather than the create then alter table.
If this isn't possible, then I'll run the extra commands.
CREATE TABLE [dbo].[Table_1](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[Disposition] [nvarchar](50) NULL,
[First] [nvarchar](50) NULL,
[Middle] [nvarchar](50) NULL,
[Last] [nvarchar](50) NULL,
[Suffix] [nvarchar](5) NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](2) NULL,
[ZipCode] [nvarchar](10) NULL,
[FileName] [nvarchar](100) NULL,
[CheckedOut] [nvarchar](5) NULL,
[CheckedOutDateTime] [datetime] NULL,
[Completed] [nvarchar](5) NULL,
[ReturnedFailed] [nvarchar](5) NULL,
[ImportedRecord] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_CheckedOut] DEFAULT ('false') FOR [CheckedOut]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_Completed] DEFAULT ('false') FOR [Completed]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_ReturnedFailed] DEFAULT ('false') FOR [ReturnedFailed]
|
|
|
|
|
Try to add the defaults to corresponding columns, like:
CREATE TABLE [dbo].[Table_1](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[Disposition] [nvarchar](50) NULL,
[First] [nvarchar](50) NULL,
[Middle] [nvarchar](50) NULL,
[Last] [nvarchar](50) NULL,
[Suffix] [nvarchar](5) NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](2) NULL,
[ZipCode] [nvarchar](10) NULL,
[FileName] [nvarchar](100) NULL,
[CheckedOut] [nvarchar](5) DEFAULT ('false') NULL,
[CheckedOutDateTime] [datetime] NULL,
[Completed] [nvarchar](5) DEFAULT ('false') NULL,
[ReturnedFailed] [nvarchar](5) DEFAULT ('false') NULL,
[ImportedRecord] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
|
|
|
|
|
Thank I didn't know what to keep and what to throw away. Thanks.
|
|
|
|
|
No problem 
|
|
|
|
|
Hello,
I am administering an ASP.NET ecommerce website that runs on top of an old legacy Oracle 8i database. I administer the database using TOAD. I have recently attempted to add a new set of flags to the master product table in order to make a set of labels appear for certain products.
The 8i database pulls information from its own store of product info, and combines it with pricing information drawn from an outside Oracle system. The final "master product" table is the result of a Materialized View (MV) or Snapshot, and this table is what the website looks at using a stored procedure when it populates a page template with product info. The process works like so:
1. PRODUCTS_TABLE plus MASTER_PRICE_TABLE are "pulled from" by the
2. Materialized View/Snapshot Script, which combines teh "downstream" data into a final table called
3. MV_MASTER_PRODUCTS (table resulting from MV)
4. MV_MASTER_PRODUCTS is called by a Proc, and this info is used to create a dynamic webpage.
Recently I added several new fields to the tables "downstream" and "upstream" of the MV/snapshot that builds the final master product table. I discovered, however, that my copy of TOAD doesn't want to let me edit the MV script itself in order to add commands to pull data up from these new fields. Although the lower levels of the database are still OK, the final MV_MASTER_PRODUCTS table (to which I added fields) is now empty, and when I attempt to refresh the MV/snapshot to populate this table, nothing happens (no error message, just no activity). Because this top-level tabel is now empty, my website can't pull data from it.
1. Is this problem caused by the fact that the PRODUCTS_TABLE and/or the MV_MASTER_PRODUCTS tables both now have additional fields that are not referenced in the MV/Snapshot script itself, and these changes are causing the script to "stall"?
2. Are MV scripts not meant to be edited, or is this just a problem with TOAD, or with my permissions? i.e., am I supposed to create a new MV to replace the old one instead of editing the existing script?
3. One final thing: I made a duplicate of the final MV_MASTER_PRODUCTS table which is still populated with the last data refresh. This is, I believe, just a regular table with no connection to a snapshot, so I don't expect it would have anything to do with MV_MASTER_PRODUCTS failing to refresh/repopulate, but I thought I should mention it just in case.
Thank you for any advice!
|
|
|
|
|
Basically materialized view is just a view (stored SQL statement), but it's been materialized to a table (predefined or not).
Nostrom0 wrote: 1. Is this problem caused by the fact that the PRODUCTS_TABLE and/or the MV_MASTER_PRODUCTS tables both now have additional fields that are not referenced in the MV/Snapshot script itself, and these changes are causing the script to "stall"?
I don't think so. A view can contain any portion of the original table(s).
Nostrom0 wrote: 2. Are MV scripts not meant to be edited, or is this just a problem with TOAD, or with my permissions? i.e., am I supposed to create a new MV to replace the old one instead of editing the existing script?
I'm not sure about TOAD, but if you modified the script (one way or another) did you execute it. I don't believe that TOAD executes it automatically. However, I haven't used TOAD very much.
What happens if you describe the materialized view (desc MV_MASTER_PRODUCTS), do you see the columns added? If not, it could be an indication that the view hasn't been changed.
Both altering and creating a new one are supported. If it would be a privilege issue you would get a clear message that you don't have the privileges.
Try taking the script from TOAD and then modify and execute it (using TOAD's SQL editor or SQL*Plus). Perhaps you would get a message describing what's wrong.
|
|
|
|
|
Hi all,
I need to relocate the datbase to another drive on my server and i need to make sure that all permissions and rules, diagrams are moved also to the new location. I can relocate either by copying and pasting mdf and ldf to new location and then attach or by backup and restore. I need to keep the same database name.
Do these above 2 ways guarantee me relocating with keeping all my permissions?
thanks
|
|
|
|
|
Yes, either detach and reattach in the new location, or backup and restore in the new location. If it is staying on the same server all these will be preserved.
If you move to a different server you will have to connect the logins (which are server wide in scope) with the users (which are database scope) but that's it.
I tend to go with the backup and restore model myself because the original is untouched until I'm satisfied everything is okay and I can take down the original. But if you are keeping the same database name you obviously can't keep the original running concurrently.
|
|
|
|
|
currently i m using ms-sql server 2000
CREATE TRIGGER [dateupdate] ON [ORMDatapro].[tab1]
FOR INSERT, UPDATE
AS
Update tab1
SET Initiated = Case WHEN Datestarted IS NULL OR LEN(Status)=0
OR (Status IN ('Cancelled', 'Cancelled merged', 'Cancelled split'))
THEN 'NA'
WHEN Status IN ('Complete', 'Close')
THEN DateDiff(dd, Signoff ,Datestarted) + 1
WHEN Status IN ('In Progress')
THEN DateDiff(dd, GetDate() ,Datestarted) + 1
END
Where Exists (Select * from INSERTED)
Here,
Datestarted, Signoff - datetime (datatype)
Status, Initiated - nvarchar (datatype)
When i save the above trigger and change any value in "tab1" table, the error Syntax error converting the varchar value 'NA' to a column of type int.
help me
|
|
|
|
|
The error message kind of says it all. Its obvious from the sql what is causing it, so why not just fix it?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
It means that Initiated (the column) is an int (a number) and 'NA' is a varchar (not a number). It expects you to put a number in a column that is marked as a number. It does not expect you to put in varchars.
|
|
|
|
|
Hi,
I have two select statements that retrieve a couple of rows as follows:
Col 1
-----
A
B
C
D
Col 2
-----
1
2
3
4
However I want to combine these two columns in one select statement to produce:
Col 1 Col 2
----- -----
A 1
B 2
C 3
D 4
Please help
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I recently did an article that included a pivot, maybe it can help you. If you are struggling with the existing examples I can only recommend you keep looking, sooner or later you will find one that makes sense.
|
|
|
|
|
I recently did an article that included a pivot, maybe it can help you. If you are struggling with the existing examples I can only recommend you keep looking, sooner or later you will find one that makes sense.
article
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes,
I've found something but it doesn't work well, maybe you can advise me as I'm not professional when it comes to SQL..
CREATE TABLE TEST(F1 NUMBER(1),F2 NUMBER(1),F3 NUMBER(1))
/
INSERT INTO TEST VALUES(1,2,3)
/
SELECT X.C1 AS "COLUMN NAME", Y.C2 AS "VALUE"
FROM
(SELECT column_name AS C1 from user_tab_columns where table_name = 'TEST') X,
(
SELECT F1 AS C2 FROM TEST
UNION
SELECT F2 FROM TEST
UNION
SELECT F3 FROM TEST
) Y
/
The bloody output is:
COLUMN NAME VALUE
------------------------------ ----------
F1 1
F1 2
F1 3
F2 1
F2 2
F2 3
F3 1
F3 2
F3 3
9 rows selected.
Why is it matching every left value with all the values?? Please help Holmes
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
This worked for me but the column names are not dynamic. You could put then into another table and do a join
<br />
--And you can keep out of my database please<br />
DECLARE @Tbl Table(F1 int,F2 int,F3 int)<br />
<br />
INSERT INTO @Tbl VALUES(1,2,3)<br />
<br />
SELECT *<br />
FROM @Tbl<br />
<br />
<br />
SELECT 'F1' Col, F1 [Value]<br />
FROM @Tbl T <br />
UNION <br />
<br />
SELECT 'F2' Col, F2 [Value]<br />
FROM @Tbl T <br />
UNION <br />
<br />
SELECT 'F3' Col, F3 [Value]<br />
FROM @Tbl T <br />
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi
I've an UPDATE statement inside a stored procedure, when I execute the SqlCommand.ExecuteNonQuery() function I get a return value of -1, but I need to get the # of rows affected by the update statement.
Any Idea how to do this?
Thanks
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Add an OUT parameter to your stored procedure and using it, return the number of affected rows. To resolve it inside the procedure use @@ROWCOUNT.
|
|
|
|
|
Thanks alot, it worked! 
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
declare a variable and, after the update,
set @Var = @@Rowcount<br />
Select @Var Records
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks alot, it worked!
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Or something that can allow us to select column names from a one-row table where a specific column value exists
I mean, I want to get the column names of a table that has for example the value '1' in the first and only row of that table
Example:
A B C
-- -- --
0 0 1
The output should be:
C
Please tell me if you can make this happen from Sql.
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Saturday, December 13, 2008 3:48 AM
|
|
|
|
|
It could be done but not in 1 select statement.
A combination of a while loop to get the column names from the system info views and a select on the table to test the value of the column could acheive it.
What happens when 2 or more column have a value?
I suspect this is a really badly designed table. I recommend that you look into the data structure if it is possible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey Holms,
Mycroft Holmes wrote: I suspect this is a really badly designed table
No, it's "terribly designed" and no, there's no way I can modify the structure
Mycroft Holmes wrote: What happens when 2 or more column have a value?
It will return those Column names just like rows.. like
A
B
C
Thanks for your help though!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
I'm sorry, my knowledge is very humble when it comes to sql, I didn't know this up until now, thanks Holmes