>1) SELECT * FROM TEST WHERE posted = 1;
> [(‘inv’, 100, 1)]
>
>2) SELECT * FROM TEST WHERE posted = ‘1’;
> [(‘inv’, 100, 1)]
>
>3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
> [(‘inv’, 100, 1)]
>
>4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
> []
>
>
>
>I am a little confused though why bullet point 2 doesn't get applied then, and
>the 1 from the case expression isn't treated as text.
The constant '1' (of storage class TEXT) has no affinity, neither has the
constant 1 (of storage class INTEGER), nor the result of an expression - with
documented exceptions, notably CAST(<value> AS <type>).
Consider:
1 = '1'
-------
0
(1=1) = '1'
-----------
0
cast((1=1) as integer) = '1'
----------------------------
1
cast((1=1) as text) = '1'
-------------------------
1
(1=1) = cast('1' as integer)
----------------------------
1
(1=1) = cast('1' as text)
-------------------------
1
The first and last CAST() examples are particularly interesting. The CAST
doesn't change it's operand's storage class (argument is already in the target
storage class); it only sets an affinity, so the subsequent comparison is
prompted to convert the result on the *other* side of the equality operator.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
