Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> Robert Duff <[EMAIL PROTECTED]> wrote:
>
> > I had a problem with inserting bad unicode characters into my database.
> > ...
> > Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units
> > ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN
> > '####################' WHEN units != '??F' THEN '!!!!!!!!!!!!!!!' ELSE
> > 'WHY DOES ONLY THE ELSE WORK???????' END FROM data LIMIT 10;"
> > "1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE
> > WORK???????",
> > ...
>
> I don't know if there's any other problem, but it looks like
> you don't understand how case expressions work. There are
> two forms, and you're confusing them. If you remove the
> first instance of 'units' in your query, it may work as you
> were expecting.
>
Aha! That's the problem. Good catch, Kurt. I thought for
a while that there was a bug in SQLite...
To clarify, you should say:
CASE
WHEN units ISNULL THEN 'a'
WHEN units='??F' THEN 'b'
WHEN units!='??F' THEN 'd'
ELSE 'd'
END
When you say this:
CASE units
WHEN units ISNULL THEN 'a'
WHEN units='??F' THEN 'b'
WHEN units!='??F' THEN 'd'
ELSE 'd'
END
It is evaluating each subexpression in between WHEN and THEN
into a boolean (an integer 0 or 1) then comparing that against
units. Since units is a string, the comparison is always false
and you end up falling through into the ELSE case.
--
D. Richard Hipp <[EMAIL PROTECTED]>
