Re: [sqlite] 'no such column' error returned in a CASE statement
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
> 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
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