View Incident Report

Minor

Reported By: Martin Kammann
Reported On: 6/9/2004
For: Version 4.04 Build 1

# 1754 SQL COALESCE Function Causing Error 10000 Invalid Field Definition with Strings >= 255 Bytes

What are the official limitations on the Coalesce() function. It appears that with string fields it only works if the field length is smaller than 255 (Error 10000). The same limitation seem to apply to IfNull().

I feel that it should work up to the maximum field length of 512.

create a table:

CREATE TABLE Test (
  TestKey GUID NOT NULL DEFAULT CURRENT_GUID,
  LongField1 CHAR(255),
  LongField2 CHAR(255),
  ShortField1 CHAR(254),
  ShortField2 CHAR(254),
  UNIQUE PRIMARY KEY (TestKey)
);


Then run these two queries (1st works, 2nd does not work):

select
  coalesce(ShortField1, ShortField2)
from
  Test;


select
  coalesce(LongField1, LongField2)
from
  Test

Comments and Workarounds

The workaround is to use the CAST() function to cast the result to a specific length.

Resolution

Fixed Problem on 6/9/2004 in version 4.08 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap