Problems With Computing A Primary Key Column
Sep 13, 2006
hi !
Just started playing around with SQL 2000 and I createda sample table called 'actor' which has 4 columns
1. actorID
(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3))
2. actorFirst
varchar(20) NOT NULL
3. actorLast
varchar(20) NOT NULL
4. actorName
(formula = [actorFirst] + ' ' + [actorLast]
Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....
I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....
What am I doing wrong ? Please help ....
View 5 Replies
ADVERTISEMENT
Oct 19, 2015
I have the following a computing column
(isnull(TotalProductSaleCost,0) * 7) / 100
I would like the output to be formatted to decimal (12, 2) not sure how to achieve this?
View 4 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Sep 13, 2007
Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
View 4 Replies
View Related
Sep 5, 2007
Hi,
I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.
We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".
I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?
Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.
Thanks for any input !
Mike123
View 3 Replies
View Related
Jul 20, 2005
Has anyone here heard or come across an article or write up about GridComputing in SQL Server 2000?Bharathihttp://www.vkinfotek.com
View 2 Replies
View Related
Mar 11, 2008
hi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
View 4 Replies
View Related
Jan 5, 2006
hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'
Can somebody pls help me with this?
Thnx in advance.
View 5 Replies
View Related
Mar 16, 2004
I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.
Essentially I have a number of different alternatives, and have found some potential solutions, including:
www.dbforums.com/arch/7/2003/9/914261
However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun
Additionally, I would like to remove Holiday's from the calculation for service level as well.
I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.
The DTD I have for the Holiday table is as follows:
CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')
Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates
Thank you in advance
View 13 Replies
View Related
Jul 6, 2013
I encountered a tricky problem. The original data, say, table_o, is shown below:
IdsStatusDate
ID1402-May-13
ID1310-May-13
ID1216-May-13
ID1120-May-13
ID2308-May-13
ID2210-May-13
ID2119-May-13
The final resulting table, e.g., table_f, is:
Ids4->3 3->2 2->1
ID18 6 4
ID2NULL 2 9
The values in the final table are the days used by each ID transferring from status i to status i-1. E.g., ID uses 8 days (10-May-13 - 2-May-13) to go to status 3 from status 4.
It is hard for me to come up with a table like the final table, although I know that the difference between two adjacent rows can be computed by using self-join and timediff().
View 3 Replies
View Related
Sep 6, 2007
Dear All.
I'm a fairly new SQL programmer so apologies if this is a silly question.
I'm trying to create a new column/variable from 3 other variables where the new column = column 1 unless column 1 is blank, then = column 2, unless column 2 is blank, then = column 3.
But I don't know where in my query to begin building this. Should I build it in a subquery? Thanks in advance for any replies.
View 2 Replies
View Related
May 17, 2008
Hi,
I want to create a view where I can calculate the sum of a couple of bit value columns,
aswell as keeping track of the total number of bits set to true.
Here is an example:
dbo.Band
BandID int
Name nvarchar(50)
Country nvarchar(50)
dbo.Record
ID int
Name nvarchar(50)
BandID int
Label nvarchar(50)
InProduction bit
InSkodne bit
From these tables I created this view:
dbo.TestView
SELECT dbo.Band.Name, dbo.Band.Country, dbo.Record.Name AS Recordname, dbo.Record.Label, CONVERT(int, dbo.Record.InProduction) AS InProduction,
CONVERT(int, dbo.Record.InSkodne) AS InSkodne, CONVERT(int, dbo.Record.InProduction) + CONVERT(int, dbo.Record.InSkodne) AS Total
FROM dbo.Band INNER JOIN
dbo.Record ON dbo.Band.BandID = dbo.Record.BandID
I use the convert function to be able to use SUM() across my bit columns, which works fine. Problems is I´m not sure that the way I´m creating the TOTAL column is the best way to go. Any other ideas?
I´m having some problems using this view and the TOTAL column in particular when referencing this view from applications outside SQL Server...
View 9 Replies
View Related
May 11, 2008
Hello all!
I´m currently devoloping an application where users can register errors related to recieved purchase orders.
I store these values in i table where the purchase order id i PK, and the possible errors that
can exist are stored as bit.
Now I want to be able to put a price on these errors.
I´m thinking about adding another table, containing all possible errors as columns, and then storing the cost of each error as an integer, and probably also a datetime for keeping track of when the costs was last updated.
I´m pretty sure this problem has been solved alot of times before, so I don´t wanna do something stupid here :-)
I´m also wondering about how it would be best to show the computed values?
Should I use a view for this?
For example:
SELECT (dbo.Orders.QuantityError * dbo.Costs.QuantityError )
FROM dbo.Costs CROSS JOIN
dbo.Record
assuming now that the Costs table only contains one row.
Is this the right way to go, or can you guys give me hints to a better solution?
Regards
Daniel
View 4 Replies
View Related
Dec 19, 2001
Is there a quick easy way to calculate rowsize for a record in a table that has data in each column?
View 5 Replies
View Related
May 8, 2007
Hello all, I known this is a SQL Server forum, but some people maybe have worked with oracle and can help me.
Microsoft's article "http://www.microsoft.com/sql/prodinfo/compare/oracle/mythandreality.mspx :"
reads as follows:
"Oracle's purported Grid enablement in 10g is based on its Oracle Real Application Clusters (RAC) technology that is no more than a local cluster. RAC is a local cluster of computers with no geographic distribution capabilities. This marketing campaign relabeled existing features to exploit current industry trends. "
My question is how can I support the above paragraph? I would like to known more reasons about why oracle grid is a local cluster instead of grid computring oriented.
Thanks in advance.
Hernán.
View 1 Replies
View Related
May 29, 2005
Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,
syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name =
'IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is the
primary key in the table ? I am reading columns for tables and
processing them, but I would like to know who is the primary key, is
that possible ?
regards
View 8 Replies
View Related
May 18, 2007
I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).
somethig like.... txtTotal.Value = FormatCurrency(txtSalesTotal.Value) - txtDiscount.Value));
Any ideas??
DNG.
View 6 Replies
View Related
Jan 22, 2007
What is the different between Primary key and Identity Column? and when should I them?
(Im just a beginner with databases...)
View 1 Replies
View Related
Apr 24, 2007
I checked column from xsd file which created from DataBase.
And before installing my application I will check any column in destination base whether have complete column or not.
And if there is any column which is not wanted column(not same as xsd structure)
I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ......"
and Execute it by program initialization.
So
I need delete it by run-time
However some column may be Primary Key with any reason
That's why I can't delete them by simple command
I expect to delete them by specific column instead of specific constraint name which is not sure name.
Please advise me....
View 2 Replies
View Related
Nov 28, 2007
Hi everyone,How do I set a primary key on an existing column using SQL statements? I have column persID of type int in table employees. How do I correctly apply the ALTER command to change persID type to INT IDENTITY(1,1) NOT NULL? Thanks in advance
View 5 Replies
View Related
Apr 5, 2000
Can anyone explain the Difference between Primary Key and Identity Column in MSSQL Server 6.5. Please give me any example if possible.
Thanks for Help!!!!
View 2 Replies
View Related
Sep 11, 2011
Can a column act as both primary key and foreign key, if so what is script.
And can a table have more than one primary keys/foreign keys, if so what is the script.
View 4 Replies
View Related
Jul 22, 2014
will there be any issues if i rename a primary key column name,which is already beging referenced as a foreign key by other tables.
View 1 Replies
View Related
Feb 9, 2015
I have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going
I have a next table called Move with the following column
ID, MoveName
I link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going
upon running query no data is shown unless i remove one of the column form the query either pickup or going ...
View 7 Replies
View Related
Feb 22, 2007
Is there a query that can return teh name of the primary key column of a table ?
View 2 Replies
View Related
Jun 25, 2006
Hello,I have a table 'customers', with 2 records:SELECT * FROM customers;customerID | customerName------------------+-------------------------myFriend | myFriend's Nametest | testing user(2 rows)but when I'm asking about customerID column, I get the answer:SELECT customerID FROM customers;ERROR: column "customerid" does not existWhat happens? I'm using PostgreSQL 8.1.3
View 4 Replies
View Related
Jul 20, 2005
Hi allWould there be a easy way to find the column name(s) which constitutea Primary constraint for a table through navigating the systemcatalogs.I found that the PK Constraint object in syscontraints is showing thecolid = 0.TIANorman
View 2 Replies
View Related
Sep 21, 2006
Hi
I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column?
Umer
View 1 Replies
View Related
May 1, 2006
hi all
my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table
i dont want to use any stored procedures only sql query
sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using
i only want to use sql query
View 3 Replies
View Related
Aug 6, 2006
given the name of any table how can i programmatically get the name of the primary key column?
View 1 Replies
View Related
Apr 22, 2008
Hello,I am in the process of making a very simple stats page that will show us how many tasks we've completed. Here is what I have so far: Here is the SQL that makes it work: SelectCommand="SELECT Count(TicketID), Category FROM Tickets GROUP BY Category ORDER BY Count(TicketID) DESC ">My problem is with how the totals seem to go on forever. Instead of being in proportion to each other as a percentage of a the total amount of tickets.. they just increase in size with each additonal entry. Can someone help me restructure this so that I can calculate the totals individually and as a whole and then apply the totals to create a proportional bar graph?Thank you greatly for your help,Mark
View 2 Replies
View Related
Jan 13, 2007
Hello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric
View 7 Replies
View Related
Sep 9, 1999
I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.
I would appreciate if someone could help me out with this.
View 4 Replies
View Related
