Just to be complete - the following works for me usually to achieve what you
seem to need from the first posted select:
Your Select:
SELECT
CASE
WHEN SEX='M' THEN 'MALE'
WHEN SEX='F' THEN 'FEMALE'
ELSE 'OTHER'
END AS p,
NAME||' - '||p AS 'NAME'
FROM DB;
The CTE version:
WITH Sx(ID, Descr) AS (VALUES ('M', 'Male'), ('F', 'Female'), ('O', 'Other'))
SELECT DB.NAME||' - '||Sx.Descr
FROM DB LEFT JOIN Sx ON Sx.ID=DB.SEX;
Cheers!
On 2014/04/28 21:27, Staffan Tylen wrote:
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
