Hi All, I have been testing a view which uses a fairly complex join between several tables and nested case expressions to select the desired value to be retruned. I believe this working as expected.
I am getting an unexpected result when I try to apply a WHERE condition to one of the returned columns. I'm not sure if this behaviour is correct or not, so I though I would ask here. The following two queries and results show what I am seeing. sqlite> select * from device_property_list where device_property_id = 26; device_id device_kind_name device_property_id device_property_name device_property_value ---------- ---------------- ------------------ -------------------- ---- ----------------- 1 System 2 Tone 3 Tone 4 Tone 5 Tone 6 Tone 7 Exchange 8 Exchange 9 Master 10 Master 11 Master 12 Master 13 Master 14 Master 15 Station Template 16 Station Template 17 Station 26 Call Announce Tone 2 18 Station 26 Call Announce Tone 2 19 Station 26 Call Announce Tone 2 20 Station 26 Call Announce Tone 2 21 Station 26 Call Announce Tone 2 22 Station 26 Call Announce Tone 2 23 Station 26 Call Announce Tone 2 24 Station 26 Call Announce Tone 2 25 Station 26 Call Announce Tone 2 26 Station 26 Call Announce Tone 2 27 Station 26 Call Announce Tone 2 28 Station 26 Call Announce Tone 2 29 Station 26 Call Announce Tone 2 30 Station 26 Call Announce Tone 3 31 Master 32 Station 26 Call Announce Tone 2 33 Station 26 Call Announce Tone 2 34 Station 26 Call Announce Tone 2 35 Station Group 36 Exchange 37 Exchange 38 Station 26 Call Announce Tone 2 39 Station 26 Call Announce Tone 2 sqlite> select * from device_property_list where device_property_id notnull and device_property_id = 26; device_id device_kind_name device_property_id device_property_name device_property_value ---------- ---------------- ------------------ -------------------- ---- ----------------- 17 Station 26 Call Announce Tone 2 18 Station 26 Call Announce Tone 2 19 Station 26 Call Announce Tone 2 20 Station 26 Call Announce Tone 2 21 Station 26 Call Announce Tone 2 22 Station 26 Call Announce Tone 2 23 Station 26 Call Announce Tone 2 24 Station 26 Call Announce Tone 2 25 Station 26 Call Announce Tone 2 26 Station 26 Call Announce Tone 2 27 Station 26 Call Announce Tone 2 28 Station 26 Call Announce Tone 2 29 Station 26 Call Announce Tone 2 30 Station 26 Call Announce Tone 3 32 Station 26 Call Announce Tone 2 33 Station 26 Call Announce Tone 2 34 Station 26 Call Announce Tone 2 38 Station 26 Call Announce Tone 2 39 Station 26 Call Announce Tone 2 sqlite> In the first case the condition is simply WHERE column = value. In this case I get many result rows where the value is NULL instead of the specified value. In the second case the condition is expanded to WHERE column NOTNULL AND column = value. This produces the result I expected for the first query. Doesn't equal to imply not null? Does this seem right to anyone, or is it a bug? Any expanation or clarification will be appreciated. Dennis Cote --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
