[sqlite] SELECT alias name limitation

[Staffan Tylen]

Why is p not valid here:

SELECT
CASE
 WHEN SEX='M' THEN 'MALE'
 WHEN SEX='F' THEN 'FEMALE'
 ELSE 'OTHER'
END AS p,
NAME||' - '||p AS 'NAME'
FROM DB;

when it's valid here:

SELECT
CASE
 WHEN SEX='M' THEN 'MALE'
 WHEN SEX='F' THEN 'FEMALE'
 ELSE 'OTHER'
END AS p,
NAME
FROM DB
ORDER BY p;

Sometimes one can use the alias name and sometimes not, why this
inconsistency? By allowing p in the first example one can eliminate a lot
of coding efforts when a complex CASE statement is used multiple times
before the FROM keyword. Or is there another way to reuse such
constructions? (Thinking about it maybe WITH could be used, but that
doesn't answer the first question.)

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