Re: [sqlite] Problem with CASE in WHERE clause

2016-12-07 Thread Hick Gunter
>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’

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-06 Thread Simon Slavin
On 6 Dec 2016, at 3:36pm, David Raymond wrote: > It seems that the CASE expression results in NO AFFINITY as an expression, > and does not carry the affinity of "posted" even though it is a direct > reference to the field. Therefore the first bullet point in section

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-06 Thread David Raymond
http://www.sqlite.org/datatype3.html section 3+ When you declare a field as type "bool" it gets assigned NUMERIC type affinity. "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Olivier Mascia
> Le 5 déc. 2016 à 12:36, Simon Slavin a écrit : > > And defining a column with a type of INTEGER is as close to BOOL as SQLite > gets. It is even better when you take into account that SQLite internally has a storage type for 'integer value 0' and 'integer value 1'

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Jean-Christophe Deschamps
Hi Simon, At 11:41 05/12/2016, you wrote: On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps wrote: > The choice of literals representing true and false is merely cosmetic. You got me interested in that. I had thought that "TRUE" and "FALSE" were reserved words in

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Jean-Christophe Deschamps
At 12:18 05/12/2016, you wrote: From: Jean-Christophe Deschamps Sent: Monday, December 05, 2016 9:48 AM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause > > At 06:29 05/12/2016, you wrote: > >My app supports sqlite3, SQL Server, and PostgreSQL. >

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Simon Slavin
On 5 Dec 2016, at 11:18am, Frank Millman wrote: > So if I understand correctly, it makes sense to use ‘1’/’0’ to *set* the > boolean value in a cross-database manner, but there are a variety of ways to > test for it. Rather than the strings it would be better to use the

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Frank Millman
From: Jean-Christophe Deschamps Sent: Monday, December 05, 2016 9:48 AM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause > > At 06:29 05/12/2016, you wrote: > >My app supports sqlite3, SQL Server, and PostgreSQL. > > > >SQL Server h

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-05 Thread Simon Slavin
On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps wrote: > The choice of literals representing true and false is merely cosmetic. You got me interested in that. I had thought that "TRUE" and "FALSE" were reserved words in SQLite. But I can find nothing to back that up,

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Jean-Christophe Deschamps
At 06:29 05/12/2016, you wrote: My app supports sqlite3, SQL Server, and PostgreSQL. SQL Server has a ‘bit’ data type, which accepts 1/0 and ‘1’/’0’ as valid values. PostgreSQL has a ‘bool’ data type, which supports a variety of values ­ TRUE, ‘t’, ‘true’, ‘yy’,

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Frank Millman
From: Don V Nielsen Sent: Sunday, December 04, 2016 5:15 PM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause > Sorry, but the whole scenario is messy, at best. The column is declared bool, and then a string '1' is assigned to it. The case lacks an else,

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Don V Nielsen
<fr...@chagford.com> wrote: > > From: Simon Slavin > Sent: Sunday, December 04, 2016 10:26 AM > To: SQLite mailing list > Subject: Re: [sqlite] Problem with CASE in WHERE clause > > > > On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote:

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Frank Millman
From: Simon Slavin Sent: Sunday, December 04, 2016 10:26 AM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause > On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote: > > > If a column contains a ‘1’, I would expect sqlite3 t

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Simon Slavin
On 4 Dec 2016, at 6:55am, Frank Millman wrote: > If a column contains a ‘1’, I would expect sqlite3 to return true when > testing for ‘1’, but in my example it returns false. I think I’ve found the problem. Here is your syntax: SELECT * FROM test_1 WHERE CASE WHEN

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Frank Millman
From: Simon Slavin Sent: Sunday, December 04, 2016 8:42 AM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause On 4 Dec 2016, at 8:42am, Simon Slavin slav...@bigfraud.org wrote: > What are we allowed to change ? Are you asking us to find a "SELECT&

Re: [sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Simon Slavin
On 4 Dec 2016, at 5:33am, Frank Millman wrote: > I really need this to work What are we allowed to change ? Are you asking us to find a "SELECT" command which works both in SQLite and PostgreSQL ? Can we change the table definition too ? Simon.

[sqlite] Problem with CASE in WHERE clause

2016-12-03 Thread Frank Millman
Hi all I have a problem using a CASE statement in a WHERE clause. On testing a column which contains a ‘1’, WHERE returns true if I test for 1 (without the quotes), but false if I test for ‘1’ (with quotes). CREATE TABLE test_1 (tran_type TEXT, amount INTEGER, posted BOOL) INSERT INTO test_1