At 01:47 PM 3/06/2005 +0000, you wrote:

>Hi Helen,
>
>Ok, I've figured out what was wrong, but can you answer something for
>me? If in the first select one of my columns types is defined as
>smallint and I cast the same column of the second select as an integer
>why does the engine give me grief over that?

Because an integer can't fit into a smallint.

>Especially since -1 is -1 in both sets?

But -1 smallint is 2 bytes, whereas -1 integer is 4.

>I know that a smallint is 16bit where as integer is 32 bit but why is
>the engine so picky about that? Does the engine compare the sizeof
>the datatypes being used as well as the values of the datatypes?

Sure does. The rule for unions is that "corresponding columns must be
compatible for size and type". If the first set defined FieldX as integer,
there would be no problem for the engine to convert smallint in the
corresp. column of another set to integer - a smallint can always fit into
an integer. But integer can't be converted to smallint.

In Fb 2, the alignment of types in unions follows more flexible rules but,
for now, you gotta do it strictly by the book.

./hb