multiple columns in rules - 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

SQL Server 2000 Forums

SQL Server Development (2000)

multiple columns in rules

Author

Topic

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-26 : 09:42:37

hey guys,
Im new here. I have three columsn in a table named XYZ. The column names are M,N,O,X, Y, and Z. I want to create a rule that will check if either of the three columns (X, Y or Z) have been filled in. If all three are empty then the record cant be saved. How can i do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 10:10:30

Use COALESCE.

SELECT *
FROM XYZ
WHERE COALESCE(X, Y, Z) IS NULL

Or

SELECT *
FROM XYZ
WHERE X IS NULL AND Y IS NULL OR Z IS NULL

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-26 : 10:56:22

How do you send data to the table?
If you use frint end application, check there and send only valid data to the table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-26 : 10:59:12

quote:
Originally posted by Peso

Use COALESCE.

SELECT *
FROM XYZ
WHERE COALESCE(X, Y, Z) IS NULL

Or

SELECT *
FROM XYZ
WHERE X IS NULL AND Y IS NULL OR AND Z IS NULL

Peter Larsson
Helsingborg, Sweden

Madhivanan

Failing to plan is Planning to fail

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-26 : 13:54:11

hey madhianan,
thanks for the reply. it was very helpful. However i have stumbled upon yet another problem related to this query. Heres how it goes:

How can i get the values of multiple columns in rules? I was Googling up rules and all i could find was the method to use single column. Let me give you an example:

In an table i have many columns, three of which are X,Y & Z. I want to create a rule that checks if the sum of three columns are greater than 1000. If i do manage creating a rule and bind it to column X - how do i access the value of Y and Z from my Rule. Could some give explain. A peice of code would be really helpful.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-26 : 14:01:18

quote:
Originally posted by mridang_agarwal

A peice of code would be really helpful.

Yes it would, and sample data and expected results too...look n my hint link in my sig, and post what it tells you to post

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-26 : 15:18:57

CREATE RULE id_chk AS @id BETWEEN 0 and 10000
GO
CREATE TABLE cust_sample
(
cust_id int
PRIMARY KEY,
ref_id int,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
)
GO
sp_bindrule id_chk, 'cust_sample.cust_id'
GO

----------------------------------------------

I'm sorry I didnt follow the instructions earlier X002548. I've addded the code now. My problem is, if you look at the code you'll see that the rule checks if (@id) is between 0 and 1000. Once the rule is bound to the column "cust_id" in the "cust_sample" table, the variable @id automaticaly gets the value of "cust_id" when called. Suppose I wanted to check if the value of ref_id is between 5000 and 7500 too, what modifications would i have to make to exisitng rule? Also, now that i guess i'll have two variables in my rule, how would i bind my rule to the columns. COuld you please explain now?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-26 : 19:31:41

[code] CREATE RULE id_chk AS @id BETWEEN 0 and 10000 OR @id BETWEEN 5000 and 7500[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-26 : 21:33:19

I think a CHECK constraint may be closer to what you want.


alter table MyTable
add constraint CK_MyTable_Values_Correct
check
(
cust_id between 0 and 10000	and
ref_id between 5000 and 7500
)

CODO ERGO SUM

mridang_agarwal
Starting Member

22 Posts

Posted - 2006-09-26 : 23:53:12

CREATE RULE id_chk AS @id BETWEEN 0 and 10000 AND @rid BETWEEN 5000 and 7500

okay, how would bind this to the cust_id and ref_id column?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-27 : 00:13:40

You can't. A rule definition can only reference one value, and it's a dummy value at that. It only references a column when the rule is bound to it, not when it's defined.

If you need to compare multiple columns as part of the condition then you have to use a CHECK constraint as MVJ suggested.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

I am confused.

First you wanted to check if three columns are empty. Later you want to see if the sum of three columns across one record has the sum of 1000 or more

ALTER TABLE [dbo].[MyTable] ADD 
	CONSTRAINT [CK_MyCheckConstraint] CHECK ([col1] + [col2] + [col3] >= 1000)
GO

And now you want to see if the three columns has individual numberic ranges?

ALTER TABLE [dbo].[MyTable] ADD 
	CONSTRAINT [CK_MyCheckConstraint] CHECK (([col1] between 0 and 1000) and ([col2] between 5000 and 7500) and ([col3] between 1000 and 4123))
GO

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources