>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’
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
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
> 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'
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
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.
>
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
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
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,
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â,
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,
<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:
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
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
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&
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.
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
17 matches
Mail list logo