473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

CASE syntax in check constraint

the following syntax is accepted:

alter table DB2ADMIN.APPTS
add constraint appts_status
check (lob_appt_status in
( case when state_code = 'VA'
then
'P'
-- , 'I'
else 'X'
end
)
);

but, obviously, i want to check against an IN list, but
that syntax hoses. can't find any docs that prohibit,
but none that show it either.

testing: LUW/8.1.6
running: z/7.1.1

yes, if worse comes to worst, i'll install it as a trigger.
i want to demonstrate to the COBOLers that check constraints
can do 99.44% of what they do. <G>

thanks,
btdb

Nov 12 '05 #1
8 3306
Bob,
but, obviously, i want to check against an IN list, but
that syntax hoses. can't find any docs that prohibit,
but none that show it either.


the in syntax seems to work fine for me, and would look like this:
alter table DB2ADMIN.APPTS
add constraint appts_status
check (lob_appt_status IN ('P','I','X'))

but if you also want to check other columns, maybe something like this
would be better:
alter table DB2ADMIN.APPTS
add constraint appts_status
check ( (lob_appt_status = 'P' AND state_code = 'VA')
OR (lob_appt_status = 'X') )

ken

Nov 12 '05 #2
yeah, the literal IN syntax works OK, which is why i set to
use the CASE syntax (too much of Mr. Celko, perhaps).

i also tried the stacked and/or with no problem.

as it stands now, i've built a Before Insert Row trigger. it
works fine. the issue is going to be that the z machine
doesn't have the C compiler (so i'm told). so a Check
Constraint would be better.

a good part of the issue is get data constraints out of the
Application Code. the runtime system (servlets, of course)
would be trained to read the catalog, and generate the
need html/javascript on the fly so that constraints are always
enforced, and maintained where they belong. java twinks don't
want to hear that, needless to say.

thx,
btdb

Nov 12 '05 #3
> so that constraints are always enforced, and maintained where they
belong.

We've been pretty successful convincing developers that all simple
logic that can be expressed declartively in the database should be.
The argument is that this will guarantee 100% of the rows comply with
the logic, regardless of business rule changes to the app, data
conversions, etc, etc. And that it's easy to code to add & maintain
without errors.

On the flip-side though, they usually add the same logic to the app (to
get more specific exceptions), as well as most of the really complex
logic.

One addition that we're doing that might work for you is to schedule
regular tests of the data quality. Since this runs batch at night, it
can take 15 minutes to run with slow queries, and just emails us if
anything looks suspicious, erroneous, etc. It won't stop the data from
getting into the database, but at least we'll know within a day -
rather than after six months. And there's less question about the
quality of the data - when you can show the results of a hundred
automated tests.

ken

Nov 12 '05 #4
CHECK(lob_appt_status in
(CASE
WHEN state_code = 'VA'
AND lob_appt_status IN ('P', 'X', ..)
THEN 1
ELSE 0 END = 1)

You can get pretty fancy with nested CASE expressions to simulate
logical implication.

Nov 12 '05 #5

--CELKO-- wrote:
CHECK(lob_appt_status in
(CASE
WHEN state_code = 'VA'
AND lob_appt_status IN ('P', 'X', ..)
THEN 1
ELSE 0 END = 1)

You can get pretty fancy with nested CASE expressions to simulate
logical implication.


i won't be able to check until i get back to CubeLand (it'll
be the first thing i do). this bit

END = 1

has always struck me as odd.

thx,
btdb

Nov 12 '05 #6
>>this bit "END = 1" has always struck me as odd. <<

Not really; it is more compact than using 'TRUE' and 'FALSE' to signal
the value of the predicates. The really fun one is

WHERE
CASE
WHEN blah-blah THEN 1
WHEN blah-blah THEN 1
WHEN blah-blah THEN 2
...
ELSE 0 END IN (SELECT level FROM CurrentScoring);

You can really fancy with this stuff :) I just emailed the manuscript
fort the third edition of SQL FOR SMARTIES tonight, so look for it at
the major bookstores in the middle of the year.

Nov 12 '05 #7
BobTheDatabaseBoy wrote:
the following syntax is accepted:

alter table DB2ADMIN.APPTS
add constraint appts_status
check (lob_appt_status in
( case when state_code = 'VA'
then
'P'
-- , 'I'
else 'X'
end
)
);

but, obviously, i want to check against an IN list, but
that syntax hoses. can't find any docs that prohibit,
but none that show it either.


CASE is a scalar expression and you can't return a table that way. So you
have to nest the IN inside the THEN/ELSE branches of the CASE.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #8
got it.

thx guys, and a tip of the Hatlo Hat.

btdb

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Brian Basquille | last post by:
Just started learning SQL recently. But one thing i'm still not clear on is about altering relationships between tables after they've been created. Instead of creating a foreign key when the...
8
by: Jan van Veldhuizen | last post by:
The UPDATE table FROM syntax is not supported by Oracle. I am looking for a syntax that is understood by both Oracle and SqlServer. Example: Table1: id name city ...
4
by: Baoqiu Cui | last post by:
Hi, I was playing with MySQL (4.1.9) during the weekend, but noticed a minor problem and would like someone to explain this to me. Basically I could successfully create a table with one unique...
8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
2
by: Alicia | last post by:
Does anyone know why I am getting a "Syntax error in Create Table statement". I am using Microsoft Access SQL View to enter it. Any other problems I may run into? CREATE TABLE weeks (...
2
by: A Bruce | last post by:
hello, I am attempting to convert a oracle database to postgresql and I am having some problems creating a constraint across multiple columns which are a mixture of case insensitive and case...
8
by: Jeff Gilbert | last post by:
Hello all. I'd appreciate some help with this one: First the DDL: CREATE TABLE ( NOT NULL , NULL , NOT NULL , (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , NOT NULL...
3
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql,...
26
by: Alexander Korsunsky | last post by:
Hi! I have some code that looks similar to this: -------------------------------------------- char array = "abcdefghij"; for (int i = 0; i < 10; i++) {
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.