At 11:51 PM 4/06/2003 -0700, you wrote:

>Thanks for answering, but I guess I'm still a bit confused. I'm looking
>at a book called "SQL for Smarties" by Joe Celko and he states in it:
>
> "... or to verify that a column's value is in an
> enumerated set, such as CHECK (sex IN ('M','F')), with
> this constraint. Remember that the sex column could also
> be set to NULL unless a NOT NULL constraint is also added
> to the column's declaration."
>
>Is that statement incorrect, or is there a difference between how the IN
>operator works and how the '"TAX" >= 0 AND "TAX" <= 0.1' formula I used
>works?

I would say that the statement is neither correct nor incorrect - just that
Joe wasn't talking about Fb/IB. He uses Oracle. I can't even cite what
the standard says, either, although IB/FB are, overall, more
standards-compliant than Oracle. (Diane Brown, where are you when we need
you?) It's not conditioned by the choice of operator in the
constraint. Here's an example I just did to demonstrate Fb's behaviour:

create table test_for_null (
id integer,
sex char(1) );

commit;

alter table test_for_null
add constraint check_values
check (sex in ('M', 'F')) ;

commit;

insert into test_for_null (id)
values (1) ;

Error:

ISC ERROR CODE:335544558

ISC ERROR MESSAGE:
Operation violates CHECK constraint CHECK_VALUES on view or table TEST_FOR_NULL

> Would 'TAX between 0.00 and 0.10' and 'TAX>=0 AND TAX<=0.1' be
> equivalent or are there subtle differences?

The comparison COLUMN_NAME BETWEEN M AND N is the same as COLUMN_NAME >= M
AND COLUMN_NAME <= N, so the only advantage in that aspect is code clarity.

On the numbers side, yes, there are arithmetical differences which will
vary according to data type. For example, with a floating-point type, it
is rare for zero to be stored as exactly zero. It is going to be something
like 0.00000000002. This makes tests involving zero quite flukey.

At some scales, scaled numerics will be stored as DOUBLE PRECISION. Hence,
it's mathematically wise to do comparisons of scaled numerics using the
same scale as the stored number. So, for a numeric(2,2), .00 will match
where 0 may or may not.

> > 1) Avoid using quoted identifiers for db objects except in places where
> > you actually need to.
>
>The Interbase DataPump tool actually did that for me automatically (at
>least the way I had it set -- I'm still trying to get a handle on the
>what all the settings really mean). One of the problems I'm having
>moving the data from Paradox is that there are lots of column name
>issues (columns named "Date", "Time", "Group", and names with spaces in
>them). I can easily strip the quotes off except in the case of invalid
>column names -- is that a good thing to do? Any particular reason why?

No: there's no hard-and-fast rule. If you can't part from these column
names, and they are not isolated incidences, then consistency throughout
the database might overrule the desire for simplicity in application
development. The problem with quoted identifiers is that, once they are
defined with quotes, they become case-sensitive AND always have to be
referred to with quotes in any SQL and application object references. That
can be a LOT of extra work, certainly much more than altering the column
names if the distribution isn't wide.

Because I code in Delphi, and use parameters extensively, my overpowering
preference is to get rid of all need to use quoted identifiers. Otherwise,
I'm going to be writing a lot of code like this:

MyDataset.ParamByName("Middle_Name").AsString := strMidName;

and this will return a Column Not Found exception:
MyDataset.ParamByName("MIDDLE_NAME").AsString := strMidName;

I know of others (esp. C++ programmers) who WANT the quoted identifiers
because they prefer the capability of having case-sensitive identifiers
and/or using "camel case" for identifiers, e.g. "MiddleName". It really
gets down to the colour of your parachute, doesn't it?

>The Interbase Datapump actually created a domain statement for me -- I
>just don't know how it works yet.

Let's say your domain is

CREATE DOMAIN D_BOOLEAN AS CHAR
CHECK (VALUE IS NULL OR UPPER(VALUE) IN ('T', 'F') ) ;

Then, anywhere in your database, you can do this:

CREATE TABLE SOMETABLE (
ID INTEGER,
IS_LEGAL D_BOOLEAN) ;

-- and that column will conform to the constraints applying to the
domain. You can apply multiple constraints to the domain. If, later, you
want to alter the domain and add more constraints to it (and it's the kind
of constraint that CAN be added after the fact), then the change will
immediately apply to all database columns that use the domain.

One sad thing about domains is that they can't (at present) be used to
declare variables in stored procedures. I believe it's on the wish-list
for Firebird 2, though.

heLen