I had a problem with inserting bad unicode characters into my database.
In the proccess of running an upgrade, I thought I would filter units
like '??F' into '°F'. I believe I have the right logic, but the WHEN
expressions of my CASE statement never seem to fire, only the ELSE
expression.
For example, the following is returned by "SELECT
locationsFK,variable,logindex,units FROM data LIMIT 10;" (yes some
fields are NULL)
"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "??F",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "??F",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "",
"1", "NVE_SCCPRGnvoSpaceTemp", "5", "??F",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "",
"0", "", "0", "",
Returned by "SELECT locationsFK,variable,logindex, (substr(units,1,2) ==
'??') FROM data LIMIT 10;"
"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "1",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "1",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "0",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "0",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "0",
"1", "NVE_SCCPRGnvoSpaceTemp", "5", "1",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "0",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "0",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "0",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "0",
"0", "", "0", "0",
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???????",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_SCCPRGnvoSpaceTemp", "5", "WHY DOES ONLY THE ELSE WORK???????",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "WHY DOES ONLY THE ELSE
WORK???????",
"0", "", "0", "",
I've tested in my program and from the command line tool. This is the
explain from the last statement, but I've never tried to read one
before. I thought I'd include it to aid anyone who attempts to help me.
addr|opcode|p1|p2|p3
0|Integer|10|0|
1|MustBeInt|0|0|
2|MemStore|0|0|
3|IfMemZero|0|50|
4|IfMemPos|0|8|
5|Pop|1|0|
6|MemInt|-1|1|
7|Goto|0|9|
8|MemStore|1|1|
9|Goto|0|51|
10|Integer|0|0|
11|OpenRead|0|549|
12|SetNumColumns|0|6|
13|Rewind|0|49|
14|Column|0|0|
15|Column|0|3|
16|Column|0|1|
17|Column|0|5|
18|Integer|1|0|
19|Column|0|5|
20|IsNull|1|22|
21|AddImm|-1|0|
22|Dup|1|1|
23|Ne|354|27|collseq(BINARY)
24|Pop|1|0|
25|String8|0|0|#!#!#!#!#!#!#!#!#!#!
26|Goto|0|45|
27|Column|0|5|
28|String8|0|0|??F
29|Eq|98|0|collseq(BINARY)
30|Dup|1|1|
31|Ne|354|35|collseq(BINARY)
32|Pop|1|0|
33|String8|0|0|####################
34|Goto|0|45|
35|Column|0|5|
36|String8|0|0|??F
37|Ne|98|0|collseq(BINARY)
38|Dup|1|1|
39|Ne|354|43|collseq(BINARY)
40|Pop|1|0|
41|String8|0|0|!!!!!!!!!!!!!!!
42|Goto|0|45|
43|Pop|1|0|
44|String8|0|0|WHY DOES ONLY THE ELSE WORK???????
45|Callback|4|0|
46|MemIncr|-1|0|
47|IfMemZero|0|49|
48|Next|0|14|
49|Close|0|0|
50|Halt|0|0|
51|Transaction|0|0|
52|VerifyCookie|0|53|
53|Goto|0|10|
54|Noop|0|0|
If I replace '"WHY DOES ONLY THE ELSE WORK???????"' with 'units', the
correct units get spit out, so the case seems to go through the rows
fine, but the comparisons just don't work there. Also, I've tried '=',
'==', '<>', and '!=' as comparison operators, no changes. I have my
string substitution code worked out nicely so all I need is the CASE
statement to work. Thank you.
--
Robert J. Duff
EnergyPro Services
