It's not inconsistent at all - You cannot use an Alias in the same bit of a statement that creates the alias, but can use it freely
in any next section. This is the documented behaviour at least - so it has nothing to do with "sometimes", it is quite specific.
So to break down your SQL:
SELECT
CASE
WHEN SEX='M' THEN 'MALE'
WHEN SEX='F' THEN 'FEMALE'
ELSE 'OTHER'
END AS p,
NAME||' - '||p AS 'NAME'
FROM DB;
Section 1:
--------------
SELECT CASE WHEN SEX='M' THEN 'MALE' WHEN SEX='F' THEN 'FEMALE' ELSE 'OTHER'
END AS p, NAME||' - '||p AS 'NAME'
Section 2:
--------------
FROM DB;
and the next bit:
SELECT
CASE
WHEN SEX='M' THEN 'MALE'
WHEN SEX='F' THEN 'FEMALE'
ELSE 'OTHER'
END AS p,
NAME
FROM DB
ORDER BY p;
Section 1:
--------------
SELECT CASE WHEN SEX='M' THEN 'MALE' WHEN SEX='F' THEN 'FEMALE' ELSE 'OTHER'
END AS p, NAME
Section 2:
--------------
FROM DB
Section 3:
--------------
ORDER BY p;
So in this last notice, the alias created within section 1 is used in section 3 - which is perfectly valid. In the one above though,
you are trying to use the alias p in the very same section as where you create it - the query engine does not already know what _p_
means before that section is "handled".
As to whether this behaviour is perfect or preferable or SQL standard I cannot speak really, and I possibly lean towards making it
available in the same section too (it might add a lot of complexity for the engine though, I don't know), but the point I'm making
is simply that it IS documented, it IS consistent and it IS expected - not in anyway random or "sometimes" as was your contention.
Hope it clears that up!
Ryan
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
