can this be done with a check constraint?

i have a zip code table downloaded from the usps in excel and imported
into a sql database table.
zipcode is not a primary key in the table, as it lists all the cities
and aliases for cities that include each zipcode.
however there is a rule in the data:
for any given zipcode, only ONE row of data can have a citytype value
of 'D'
what i am wondering is how i can turn that rule into a database level
constraint. i can't create a unique constraint on zipcode + citytype,
because any number of rows for the same zipcode can have citytype N and
A, for example. it is only the D value that can appear only once per
zipcode.
is this something a check constraint can accomplish? i'm new to check
constraints so i'm not sure what that constraint rule would look like.
it seems more complicated than "thisfield > thatfield" type logic.
thanks,
jasonI think this is beyond the capabilities of a check constraint.
A Check constraint is an expression which is used to check that the contents
of a column conform to a given rule. The constraint cannot reference
information on a different row of data than the one which is being checked.
You may be able to accomplish your scenario using a trigger. There are
times however, then the integrity checking would be so expensive that it's
simply more efficient to push said rule to a different business logic layer.
Colin.
"jason" <iaesun@.yahoo.com> wrote in message
news:1142884753.165122.99270@.u72g2000cwu.googlegroups.com...
>i have a zip code table downloaded from the usps in excel and imported
> into a sql database table.
> zipcode is not a primary key in the table, as it lists all the cities
> and aliases for cities that include each zipcode.
> however there is a rule in the data:
> for any given zipcode, only ONE row of data can have a citytype value
> of 'D'
> what i am wondering is how i can turn that rule into a database level
> constraint. i can't create a unique constraint on zipcode + citytype,
> because any number of rows for the same zipcode can have citytype N and
> A, for example. it is only the D value that can appear only once per
> zipcode.
> is this something a check constraint can accomplish? i'm new to check
> constraints so i'm not sure what that constraint rule would look like.
> it seems more complicated than "thisfield > thatfield" type logic.
> thanks,
> jason
>|||Jason,
2 ways to accomplish that:
1. google up "nullbuster" and create an index on computed columns
2. create an indexed view for
select zipcode from mytable where citytype = 'D'
Good luck!sql

Can SQLServer produce Excel Spreadsheet output ?

Deaa group,

I am using SQLServer 2000 in an XP Sp2. I would like to do the
following:

I have a program running on a database server that generates some data
which are loaded to the database. This program is used in a web
application, invoked by some java program and JSP scripts. (I am
frontend illiterated.)

The question is, is it possible to write a stored procedure to generate
output in excel spreadsheet? So that user could call this procedure
and get spreadsheet output on the client side.

Any pointer to a solution would be immensely apprecaited.

thanks,
charia<cpeters5@.gmail.com> wrote in message
news:1120580708.814110.191080@.z14g2000cwz.googlegr oups.com...
> Deaa group,
> I am using SQLServer 2000 in an XP Sp2. I would like to do the
> following:
> I have a program running on a database server that generates some data
> which are loaded to the database. This program is used in a web
> application, invoked by some java program and JSP scripts. (I am
> frontend illiterated.)
> The question is, is it possible to write a stored procedure to generate
> output in excel spreadsheet? So that user could call this procedure
> and get spreadsheet output on the client side.
> Any pointer to a solution would be immensely apprecaited.
> thanks,
> charia

As far as I know, there's no direct way to export to an .xls from a stored
proc. DTS can export data to Excel, and you can execute a package from a
stored proc in various ways:

http://www.sqldts.com/default.aspx?210

By using ActiveX steps in a DTS package, you could control all the details
of the .xls file name, structure, column headers etc. via the Excel COM
interface, but you would need to actually install Excel on the server in
order to do that, which may not be possible (or desirable).

Another option would be calling bcp.exe via xp_cmdshell to create a CSV or
tab-delimited file. In the end, the easiest solution might be to find a Java
or JSP module of some sort which can export to Excel - then you just return
the result set to the client or middle tier as usual, and let it create the
file, which is probably a cleaner solution than dealing with presentation in
the database itself.

Simon|||i know ASP can generate an xls from data selected by a SP. i bet there
is some way JSP can do it as well, i'm just not a web developer =P