Thanks Helen.

The problem was graves instead of opening single inverted commas. I
should have noticed there was no syntax highlighting. I first wrote
the query in an Outlook email, and thought I had better test it
before sending. Weirdly, cutting and pasting from Outlook to
FlameRobin replaces opening inverted commas with Graves!!! At least
on my Vista system it does - I just tried it again. Presumably it's
to do with the character table setting in the OS, but I can't be
bothered to find out.

On your other points:

Varchar(1) means either an empty string or a string of length 1.

The domain definition was cut and pasted from a Flamerobin DDL
report, and putting double quotes instead of single quotes around
default values seems to be a Flamerobin error. I'd better let Milan
know.

The reason I am casting a char column to a varchar is so that the
query will still run if used on a database in which the field has
already been converted to an integer. (It won't then do anything, but
can be part of a more elaborate script, and will not prevent the rest
of the script from running. FB probably handles the conversion from
int to char perfectly well without the cast - but just in case...).


Roger Vellacott
Passfield Data Systems Ltd



--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 08:48 PM 24/02/2008, you wrote:
> >Can anyone explain why this doesn't work? (It is part of a

utility

> >to sort out disagreement between database versions where one uses

T

> >and F as boolean values, and the other 1 and 0).
> >
> >UPDATE MY_TABLE SET MY_BOOL =
> > CASE
> > WHEN (CAST(MY_BOOL AS VARCHAR(1)) = `T') THEN `1'
> > ELSE `0'
> > END
> >
> >The field MY_BOOL is defined with the domain "BOOLEAN", which is
> >described as follows;
> >
> >CREATE DOMAIN "BOOLEAN"
> > AS Char(1)
> > DEFAULT "F"
> >
> >FB 2.03.
> >
> >Under IB_SQL it reports "unexpected end of command" on line 3.
>
> Double-quotes on the default? Should be DEFAULT 'F'? And if

you're running the statement in a script it needs a statement
terminator on line 3.

>
> >Under Flamerobin it reports "SQL statement can't be 0."
> >
> >Am I having a "senior" moment here?
>
> cast? you're reading a CHAR column. Why would you cast it as

something else? And wot the 'eck be a varchar(1) anyhows????????

>
> UPDATE MY_TABLE SET MY_BOOL =
> CASE
> WHEN 'T' THEN '1'
> ELSE '0'
> END; <-- if it's in a script(
>
> And make sure your left single-quotes really are single quotes and

not graves (as they are in your posting).

>
> ./heLen
>