Identifying a valid Record based on a Rule

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

SQL Server 2000 Forums

Transact-SQL (2000)

Identifying a valid Record based on a Rule

Author

Topic

Vatsan
Starting Member

7 Posts

Posted - 2006-01-27 : 02:29:36

There is a source table with six columns.

The rule of inserting the records into this table is:
Only one of the column for a record shud have non-zero and non–null value. For example (0 0 4 0 null 0) is a valid record.
But unfortunately some records violating the rule has got into the table.

I need to write a query to return all the records in the table
(1) which satisfies the rule.
(2) that doesnot satisfy the rule.

Note:
1. Should be the most efficient SQL query to this situation.
2. Shud not change the physical strcture of the existing table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-27 : 02:40:14

[code]-- valid record
select *
from yourtable
where col1 = 0
and col2 = 0
and col3 <> 0
and col4 = 0
and col5 is null
and col6 = 0

-- invalid record
select *
from yourtable
where not
(col1 = 0
and col2 = 0
and col3 <> 0
and col4 = 0
and col5 is null
and col6 = 0)[/code]

----------------------------------
'KH'

Vatsan
Starting Member

7 Posts

Posted - 2006-01-27 : 02:59:36

Hi khtan,

Thanks for your speedy response.

I am afraid, You have not understood the issue.

May be I could put it this way...
Only one of the columns can have a valid number and the rest can have a null or 0.

Valid Records
ex: C1 C2 C3 C4 C5 C6
---------------------------
0 0 4 0 null 0
null 5 0 0 0 null
2 null null 0 0 0

Invalid Records
ex: C1 C2 C3 C4 C5 C6
---------------------------
5 6 0 0 null 0

I can use cursors to scan each record and check for the rule and fetch them.

This will directly be put into an already running code.
So I want the most effecient way of doing this.

Thanks,
Vatsan

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-01-27 : 03:59:39

Hi Vatsan
The following should return all rows that violate the rule. Just change the condition in the WHERE clause to =1 to return those that satisfy the rule.

SELECT
	mt.*
FROM
	dbo.MyTable AS mt
WHERE
	CASE 
		WHEN
			COALESCE(mt.Col1, 0) = 0
		THEN
			0
		ELSE
			1
	END
	+
	CASE 
		WHEN
			COALESCE(mt.Col2, 0) = 0
		THEN
			0
		ELSE
			1
	END
	--...
	--repeat for all columns
	--...
	+
	CASE 
		WHEN
			COALESCE(mt.Col6, 0) = 0
		THEN
			0
		ELSE
			1
	END
	> 1

Additionally, if it is possible that some records have no non-zero, non-null values, change the condition to = 0.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources