SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaganreddy
Starting Member

India
10 Posts

Posted - 08/21/2013 :  01:39:34  Show Profile  Reply with Quote

Hi
iam new to this forum, i have a some doubts. pls clarify me if ny one .
i have 3 fields in my table called as GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique. Duplicates (including the original record) must be rejected.

At the same time each column should cannot be null.

GLB_ID column should be a number.

TECH_VLD_FROM_DTTM Should contain a valid timestamp. Eg: 31MAR2007:16:06:00.000000000
Help me any body ,
Thanks
Jagan

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/21/2013 :  01:58:45  Show Profile  Reply with Quote
>> GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique
SELECT DISTINCT GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM
FROM TableName
WHERE GLB_ID IS NOT NULL AND BUSINESS_ID IS NOT NULL AND TECH_VLD_FROM_DTTM IS NOT NULL -- any one of column should not be NULL
AND ISNUMBER(GLB_ID) = 1 -- check for numeric values


--
Chandu
Go to Top of Page

jaganreddy
Starting Member

India
10 Posts

Posted - 08/21/2013 :  02:04:22  Show Profile  Reply with Quote
quote:
Originally posted by bandi

>> GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique
SELECT DISTINCT GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM
FROM TableName
WHERE GLB_ID IS NOT NULL AND BUSINESS_ID IS NOT NULL AND TECH_VLD_FROM_DTTM IS NOT NULL -- any one of column should not be NULL
AND ISNUMBER(GLB_ID) = 1 -- check for numeric values


--
Chandu







Thnks for ur reply chandu, but, we need to apply rules for each column individually. date time field should contain valid timestamp also..

Thnks

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/21/2013 :  02:20:29  Show Profile  Reply with Quote
you are talking about table definition....
>> GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique
Add Unique constraint for the above combination
CREATE TABLE TableName
(
GLB_ID INT NOT NULL,
BUSINESS_ID INT NOT NULL,
TECH_VLD_FROM_DTTM DATETIME2 NOT NULL
)


--
Chandu
Go to Top of Page

jaganreddy
Starting Member

India
10 Posts

Posted - 08/21/2013 :  03:05:47  Show Profile  Reply with Quote
[quote]Originally posted by bandi

you are talking about table definition....
>> GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique
Add Unique constraint for the above combination
CREATE TABLE TableName
(
GLB_ID INT NOT NULL,
BUSINESS_ID INT NOT NULL,
TECH_VLD_FROM_DTTM DATETIME2 NOT NULL
)

Thanks for ur repkly chandu..

here my exact requirment is
"1. GLB_ID
a) Cannot be NULL.
b) Should be a number.

2. BUSINESS_ID
a) Cannot be NULL.
b) Length should not exceed 255 characters.

3. TECH_VLD_FROM_DTTM
a) Cannot be NULL.
b) Should contain a valid timestamp. Eg: 31MAR2007:16:06:00.000000000"

AT THE SAME TIME
A combination of GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique. Duplicates (including the original record) must be rejected.
THANKS
JAGAN
Go to Top of Page

jaganreddy
Starting Member

India
10 Posts

Posted - 08/21/2013 :  03:49:18  Show Profile  Reply with Quote
Please help me somebody ......


Thanks
Jagan
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  04:34:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What datetype do the three columns have now?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  04:42:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Depending on the current datatypes, try this
DELETE
FROM	dbo.Table1
WHERE	GLB_ID IS NULL
	OR CAST(GLB_ID AS NVARCHAR(MAX)) LIKE N'%[^0-9]%'
	OR BUSINESS_ID IS NULL
	OR LEN(LTRIM(RTRIM(CAST(BUSINESS_ID AS NVARCHAR(MAX))))) > 255
	OR TECH_VLD_FROM_DTTM IS NULL
	--OR {check for invalid dates}
Go

CREATE UNIQUE NONCLUSTERED INDEX UX_Table1 ON dbo.Table1 (GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM) WITH (IGNORE_DUP_KEY = ON);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  04:45:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Duplicates (including the original record) must be rejected


Does that mean if you already have a row with {1, 2, 3} and that row is unique,
and you try to insert a new row with {1, 2, 3} two things happen

1. The new row is not inserted as it would produce a duplicate
2. The old row is deleted




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jaganreddy
Starting Member

India
10 Posts

Posted - 08/21/2013 :  05:00:15  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

What datetype do the three columns have now?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Thanks for ur reply, the first column data type is Num nd the second column datatype is also num and the last one column data type is datetime. More over we need to apply rule to each column seperatly .
Thanks
Jagan

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  06:50:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If the three columns are "num", "num" and datetime, then all you have to check is if the values are null.
You cannot store values like a, b, c and d in a "num" column.

But then again, why would you want a check that second column content need to have a length less than 255 characters?
There is no "num" datatype in SQL Server that accepts that large number.

I believe you WANT the data to be numeric but you get crap data into the table. If you get crap data into the table
YOU ARE NOT USING PROPER DATATYPES, nor are you using "num" and "num" as you told me.

Once again, what exact datatypes are you using today (never mind the intent)?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/21/2013 :  11:00:22  Show Profile  Reply with Quote
I think we need to stop and take a step back. You haven't shown us your table DDL, so, as Peso pointed out, we don't know the proper data types. Here are some links that can help you ask your question so we can help you better without having to ask lots of questions and/or guess at what you want.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  01:03:51  Show Profile  Reply with Quote
quote:
Originally posted by jaganreddy

[quote]Originally posted by bandi

you are talking about table definition....
>> GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique
Add Unique constraint for the above combination
CREATE TABLE TableName
(
GLB_ID INT NOT NULL,
BUSINESS_ID INT NOT NULL,
TECH_VLD_FROM_DTTM DATETIME2 NOT NULL
)

Thanks for ur repkly chandu..

here my exact requirment is
"1. GLB_ID
a) Cannot be NULL.
b) Should be a number.

2. BUSINESS_ID
a) Cannot be NULL.
b) Length should not exceed 255 characters.

3. TECH_VLD_FROM_DTTM
a) Cannot be NULL.
b) Should contain a valid timestamp. Eg: 31MAR2007:16:06:00.000000000"

AT THE SAME TIME
A combination of GLB_ID, BUSINESS_ID, TECH_VLD_FROM_DTTM in each row must be unique. Duplicates (including the original record) must be rejected.
THANKS
JAGAN



Hi JaganReddy,
CREATE TABLE TableName
(
GLB_ID INT NOT NULL,
BUSINESS_ID VARCHAR(255) NOT NULL,
TECH_VLD_FROM_DTTM DATETIME2 NOT NULL,
CONSTRAINT UK_Key_1 UNIQUE ( GLB_ID , BUSINESS_ID, TECH_VLD_FROM_DTTM ) -- This is for unique combination of three columns GLB_ID , BUSINESS_ID, TECH_VLD_FROM_DTTM
)


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000