Fwd: column name as a value in a tuple - headache!

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: column name as a value in a tuple - headache!

Bjørn Eikeland
I've been using sqlite to ease making statistical graphs from
metrological data and have encountered a strange problem.

Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag'
contains the type of precipitation as a two letter code. RR (capital)
is the code for rain, but any and all select statemens includeing
WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column
'rr'..

Is this a feature or a bug? I'm not really sure where to look for
similar cases or what to call it. It seems it could have its uses, but
then one should also be able to force not to behave like that.

More generic example:
BEGIN TRANSACTION;
DROP TABLE test;
CREATE TABLE test(
        colA    TEXT,
        colB    TEXT);
INSERT INTO test VALUES("a0", "b0");
INSERT INTO test VALUES("a1", "b1");
INSERT INTO test VALUES("a2", "b2");
INSERT INTO test VALUES("a3", "colb");
INSERT INTO test VALUES("a4", "b3");
INSERT INTO test VALUES("a5", "cola");
COMMIT;

SELECT test.* FROM test where test.colB="colb";
SELECT test.* FROM test where test.colB="cola";


I would expect each of the two queries to return a single row,
however, the first query returns 5 rows, and the last no rows.

mvh,
Bjørn
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: column name as a value in a tuple - headache!

Guillaume MAISON
Bj?rn Eikeland a ?crit :

> I've been using sqlite to ease making statistical graphs from
> metrological data and have encountered a strange problem.
>
> Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag'
> contains the type of precipitation as a two letter code. RR (capital)
> is the code for rain, but any and all select statemens includeing
> WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column
> 'rr'..
>
> Is this a feature or a bug? I'm not really sure where to look for
> similar cases or what to call it. It seems it could have its uses, but
> then one should also be able to force not to behave like that.

Hi Bj?rn ,

Have you tried to use single quotes instead of double quotes ?

varchar values have to be encapsulated wihtin single quotes...

HTH,

--

Guillaume MAISON - N@uteus
83, Cours Victor Hugo
47000 AGEN
T?l : 05 53 87 91 48 - Fax : 05 53 68 73 50
e-mail : [hidden email] - Web : http://nauteus.com

Reply | Threaded
Open this post in threaded view
|

Re: Fwd: column name as a value in a tuple - headache!

Puneet Kishor
In reply to this post by Bjørn Eikeland

On Nov 6, 2005, at 8:19 AM, Bjørn Eikeland wrote:

> Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag'
> contains the type of precipitation as a two letter code. RR (capital)
> is the code for rain, but any and all select statemens includeing
> WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column
> 'rr'..

use single quotes

WHERE slag = 'RR'




--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Fwd: column name as a value in a tuple - headache!

Bjørn Eikeland
On 11/6/05, Puneet Kishor <[hidden email]> wrote:

>
> On Nov 6, 2005, at 8:19 AM, Bjørn Eikeland wrote:
>
> > Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag'
> > contains the type of precipitation as a two letter code. RR (capital)
> > is the code for rain, but any and all select statemens includeing
> > WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column
> > 'rr'..
>
> use single quotes
>
> WHERE slag = 'RR'
>
> --
> Puneet Kishor
>
>

Single quotes it is then!

Thanks!