SQL Problem - 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

SQL Problem

Author

Topic

jaganreddy
Starting Member

10 Posts

Posted - 2013-08-21 : 01:39:34

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 01:58:45

>> 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

jaganreddy
Starting Member

10 Posts

Posted - 2013-08-21 : 02:04:22

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 02:20:29

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

jaganreddy
Starting Member

10 Posts

Posted - 2013-08-21 : 03:05:47

[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

jaganreddy
Starting Member

10 Posts

Posted - 2013-08-21 : 03:49:18

Please help me somebody ......

Thanks
Jagan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 04:34:45

What datetype do the three columns have now?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 04:42:39

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 04:45:18

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

jaganreddy
Starting Member

10 Posts

Posted - 2013-08-21 : 05:00:15

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 06:50:03

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 11:00:22

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 01:03:51

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources