Can I use a 'Conditional CHECK' constraint ?

Hi SQL Gurus,
Using Sql2000, I have a table to maintain stock availibility, the
requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
constraint like below :
CREATE TABLE StockAvailibility
(prod_code CHAR(5) NOT NULL PRIMARY KEY,
qty_on_hand INTEGER NOT NULL,
qty_in_orders INTEGER NOT NULL,
CHECK (qty_on_hand >= qty_in_orders));
The problem is : user need more flexible approach, they want to decide at
initial implementation / runtime where they need this constraint, there is a
parameter table to store this setting.
CREATE TABLE App_Parameter
(Check_Stock bit);
The above CHECK constraint should only be run if App_Parameter.Check_Stock = 1.
How can I do this ?
TIA,
KristYou could possibly use a UDF which returns 0 or 1 and which checks against the other table. Watch
out, though, that if you don't refer to the column in the check constraint, then the optimizer might
not deem is necessary to validate the CHECK constraint. This can happen when you have CHECK
constraint which uses UDF can work against other tables. I don't think that it will be a problem in
your case, though. You could have something like:
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"tristant" <krislioe@.cbn.net.id> wrote in message news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Hi SQL Gurus,
> Using Sql2000, I have a table to maintain stock availibility, the
> requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> constraint like below :
> CREATE TABLE StockAvailibility
> (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> qty_on_hand INTEGER NOT NULL,
> qty_in_orders INTEGER NOT NULL,
> CHECK (qty_on_hand >= qty_in_orders));
> The problem is : user need more flexible approach, they want to decide at
> initial implementation / runtime where they need this constraint, there is a
> parameter table to store this setting.
> CREATE TABLE App_Parameter
> (Check_Stock bit);
> The above CHECK constraint should only be run if App_Parameter.Check_Stock => 1.
> How can I do this ?
> TIA,
> Krist
>
>
>|||Tibor,
Did you intend to give an example'
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> You could possibly use a UDF which returns 0 or 1 and which checks against
the other table. Watch
> out, though, that if you don't refer to the column in the check
constraint, then the optimizer might
> not deem is necessary to validate the CHECK constraint. This can happen
when you have CHECK
> constraint which uses UDF can work against other tables. I don't think
that it will be a problem in
> your case, though. You could have something like:
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "tristant" <krislioe@.cbn.net.id> wrote in message
news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > Hi SQL Gurus,
> > Using Sql2000, I have a table to maintain stock availibility, the
> > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > constraint like below :
> >
> > CREATE TABLE StockAvailibility
> > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > qty_on_hand INTEGER NOT NULL,
> > qty_in_orders INTEGER NOT NULL,
> > CHECK (qty_on_hand >= qty_in_orders));
> >
> > The problem is : user need more flexible approach, they want to decide
at
> > initial implementation / runtime where they need this constraint, there
is a
> > parameter table to store this setting.
> > CREATE TABLE App_Parameter
> > (Check_Stock bit);
> >
> > The above CHECK constraint should only be run if
App_Parameter.Check_Stock => > 1.
> > How can I do this ?
> >
> > TIA,
> > Krist
> >
> >
> >
> >
> >
>|||I guess I did, but the hunger overwhelmed me and I went for lunch ;-). Below is a very stripped down
to bare essentials example:
create table t(c1 bit)
GO
create function f() returns bit as
begin
RETURN (SELECT TOP 1 c1 FROM dbo.t)
end
GO
Create table t1(c1 int)
GO
ALTER TABLE t1 ADD CONSTRAINT x CHECK(dbo.f() = 0 OR (dbo.f() = 1 AND c1 < 10))
GO
INSERT t values(0) --Means do not check
INSERT t1 values(10)
GO
UPDATE t SET c1 = 1 --Means check
INSERT t1 values(10) --Fails
UPDATE t1 SET c1 = 11 --Also fails
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SriSamp" <ssampath@.sct.co.in> wrote in message news:%23$o2JsYuDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> Did you intend to give an example'
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > You could possibly use a UDF which returns 0 or 1 and which checks against
> the other table. Watch
> > out, though, that if you don't refer to the column in the check
> constraint, then the optimizer might
> > not deem is necessary to validate the CHECK constraint. This can happen
> when you have CHECK
> > constraint which uses UDF can work against other tables. I don't think
> that it will be a problem in
> > your case, though. You could have something like:
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > Hi SQL Gurus,
> > > Using Sql2000, I have a table to maintain stock availibility, the
> > > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > > constraint like below :
> > >
> > > CREATE TABLE StockAvailibility
> > > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > > qty_on_hand INTEGER NOT NULL,
> > > qty_in_orders INTEGER NOT NULL,
> > > CHECK (qty_on_hand >= qty_in_orders));
> > >
> > > The problem is : user need more flexible approach, they want to decide
> at
> > > initial implementation / runtime where they need this constraint, there
> is a
> > > parameter table to store this setting.
> > > CREATE TABLE App_Parameter
> > > (Check_Stock bit);
> > >
> > > The above CHECK constraint should only be run if
> App_Parameter.Check_Stock => > > 1.
> > > How can I do this ?
> > >
> > > TIA,
> > > Krist
> > >
> > >
> > >
> > >
> > >
> >
> >
>

Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.