----- Original Message -----
From: "Kurt Welgehausen" <[EMAIL PROTECTED]>
> No one can really evaluate this without knowing your view
> definition.
Here you are. I'm still not sure that it is relavent though.
CREATE VIEW Device_Property_List AS
SELECT
D.device_id AS device_id,
DK.device_kind_name AS device_kind_name,
DP.device_property_id AS device_property_id,
DP.device_property_name AS device_property_name,
CASE
WHEN DPV.device_property_value NOTNULL THEN
DPV.device_property_value
WHEN T.device_id NOTNULL THEN
CASE
WHEN TPV.device_property_value NOTNULL THEN
TPV.device_property_value
ELSE
TP.device_property_default
END
ELSE
DP.device_property_default
END AS device_property_value
FROM
Device AS D
LEFT JOIN Device_Kind AS DK
ON (D.device_kind_id = DK.device_kind_id)
LEFT JOIN Device_Property AS DP
ON (D.device_kind_id = DP.device_kind_id)
LEFT JOIN Device_Property_Value AS DPV
ON ((D.device_id = DPV.device_id)
AND (DP.device_property_id = DPV.device_property_id))
LEFT JOIN Device_Property AS DTP
ON ((D.device_kind_id = DTP.device_kind_id)
AND (DTP.device_property_name = 'Template'))
LEFT JOIN Device_Property_Value AS TID
ON ((D.device_id = TID.device_id)
AND (DTP.device_property_id = TID.device_property_id))
LEFT JOIN Device AS T
ON (TID.device_property_value = T.device_id)
LEFT JOIN Device_Property AS TP
ON ((T.device_kind_id = TP.device_kind_id)
AND (DP.device_property_name = TP.device_property_name))
LEFT JOIN Device_Property_Value AS TPV
ON ((T.device_id = TPV.device_id)
AND (TP.device_property_id = TPV.device_property_id))
;
> You can begin to investigate it by adding 'device_property_id=26'
> to the where clause of the select statement in your view
> definition. Then see if the select will execute correctly, or if
> it even makes sense.
I have tried this. I get the same results when the WHERE clauses are added
to the view's SELECT statement.
I have done some other tests as show below;
sqlite> select 26 = NULL;
26 =
----
sqlite> select 26 = 26;
26 =
----
1
sqlite> select 26 = 27;
26 =
----
0
sqlite> select 26 = 'a';
26 =
----
0
sqlite> select (select 26 = NULL) ISNULL;
(sel
----
1
sqlite> select (select 26 = 26) ISNULL;
(sel
----
0
sqlite> select (select 26 = 27) ISNULL;
(sel
----
0
It looks like the equality operator isn't really boolean. It can return
three values, true, false, or null. I suspect the VM that executes the
statement is braching when the comparison result is false. Therfore it
passes the test when the result is true (what I want) and when it is NULL
(which I don't want). In this non-boolean case not false isn't the same as
true.
I'm still not sure if this is normal behavior for SQL or not. I'm curious
what other database engines give as results for these tests.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
