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)
On Apr 28, 2014, at 9:27 PM, Staffan Tylen wrote:
> (Thinking about it maybe WITH could be used,
Yes, it’s a typical use case for WITH.
> but that doesn't answer the first question.)
One cannot refer to an identifier in the same section it was declared in, and
that’s that. Therefore ‘WITH’ a
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
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
4 matches
Mail list logo
