Solved

MSSQL - Conditional Statements in Column Formula

Posted on 2002-03-12
3
1,214 Views
Last Modified: 2007-12-19
Is it possible to have conditional statements in the column formula (when designing a table)? What is the correct syntax?
0
Comment
Question by:cdzuniga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 6857374
Yes, it is, but not by using IF.  Instead, use CASE, e.g.:

SELECT MyField1, (Case MyGender When 'M' Then 'Male' When 'F' Then 'Female' Else '???' END) As Gender
FROM MyTable
0
 
LVL 5

Accepted Solution

by:
kelfink earned 100 total points
ID: 6857889
CHECK constraints sound like what you want...
To guarantee that a salary is less than 20 or greater than 20000, use:

create table strangesalary
( salary int check ( salary < 20 or salary > 20000));

What you can't do is have one column reference others in an expression:

create table failure
( hourly int check ( hourly is not null or monthly is not null),
  monthly int );
gives error:
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'hourly' references another column, table 'failure'.

You can enforce a set of values with:

create table person
( gender char(1) check ( gender in('M','F') ) ) ;
0
 

Expert Comment

by:cafeoui
ID: 14941910
The original solution does not address the reason for the error message.

The reason for the original error message is that, with SQL Server, column CONSTRAINTS cannot reference other columns.  To apply a constraint referencing multiple columns, the CONSTRAINT must be a table constraint.

WRONG:
create table failure
( hourly int check ( hourly is not null or monthly is not null),
  monthly int );

RIGHT:
create table failure
( hourly int ,
  monthly int,
CONSTRAINT cck_f_hm CHECK ( hourly IS NOT null OR monthly IS NOT null));

Note that the constraint follows the last comma; thus, separating it from any single column...that makes it a table constraint.  This will result in a successful CREATE TABLE with the desired constraint.

Also, note that labeling the constraint with "cck_f_hm" is not required but I always name my constraints.  You never know when you may need to reference that constraint.  It's that "...ounce of prevention..." thing...  ;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question