Re: [sqlite] 'no such column' error returned in a CASE statement

[Ioannis Epaminonda]

Pavel Ivanov-2 wrote:
> 
> 
> Yes, it's expected. Column aliases are visible only in GROUP BY/ORDER
> BY/HAVING clauses and outer selects. All other places should use exact
> column expression instead.
> 
> Pavel
> 
> 

Ah, thanks Pavel for the clarification, now it makes sense.
This is a bit inconvenient but i guess i can use a VIEW for the second
select.

I noticed that the same applies for aggregate functions in the select
statement.

I was searching for some documentation in the sqlite site but didn't find
anything so i guess this is defined in the SQL standard.


-- 
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30123189.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'no such column' error returned in a CASE statement

[Pavel Ivanov]

> This works:
>
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test' as a) as errval;

I guess OP meant it like this:
select a,case when a='test' then 'true' else 'false' end as errval
from (select 'test' as a);

And to answer the question:
> Is this the expected result or should the generated column be available to
> the case statement.

Yes, it's expected. Column aliases are visible only in GROUP BY/ORDER
BY/HAVING clauses and outer selects. All other places should use exact
column expression instead.


Pavel

On Tue, Nov 2, 2010 at 10:39 AM, Black, Michael (IS)
 wrote:
> This works:
>
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test' as a) as errval;
> test|true
> sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
> ct 'test2' as a) as errval;
> test2|false
>
> I suppose there's another solution too...
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Ioannis Epaminonda
> Sent: Tue 11/2/2010 8:19 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] 'no such column' error returned in a CASE statement
>
>
>
>
> The following error 'no such column: A' is returned when i execute the
> following statement.
>
> SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
> ERRVAL
>
> Is this the expected result or should the generated column be available to
> the case statement.
> Thanks.
>
> --
> View this message in context: 
> http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'no such column' error returned in a CASE statement

[Black, Michael (IS)]

This works:
 
sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
ct 'test' as a) as errval;
test|true
sqlite> select a,case when a='test' then 'true' else 'false' end from (sele
ct 'test2' as a) as errval;
test2|false
 
I suppose there's another solution too...
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Ioannis Epaminonda
Sent: Tue 11/2/2010 8:19 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] 'no such column' error returned in a CASE statement




The following error 'no such column: A' is returned when i execute the
following statement.

SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
ERRVAL

Is this the expected result or should the generated column be available to
the case statement.
Thanks.

--
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'no such column' error returned in a CASE statement

[Ioannis Epaminonda]

The following error 'no such column: A' is returned when i execute the
following statement.

SELECT 'test' as A,CASE WHEN A = 'test' THEN 'true' ELSE 'false' END as
ERRVAL

Is this the expected result or should the generated column be available to
the case statement.
Thanks.

-- 
View this message in context: 
http://old.nabble.com/%27no-such-column%27-error-returned-in-a-CASE-statement-tp30113686p30113686.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users