Re: Urgent help needed- alias name in update statement

Поиск
Список
Период
Сортировка
От venkatrao.b@tcs.com
Тема Re: Urgent help needed- alias name in update statement
Дата
Msg-id OF2C8F7E0E.4212EB49-ON652576E2.00175A6B-652576E2.0017D70D@tcs.com
обсуждение исходный текст
Ответ на Re: Urgent help needed- alias name in update statement  (Josh Kupershmidt <schmiddy@gmail.com>)
Список pgsql-novice
Дерево обсуждения

Friends, Thank you all for your prompt responses..

Thanks Michael, for correcting me..
Actually i also used to call it  postgres earlier..but later somewhere in net i found that it is postgre and not postgres..(may be i din' read that time properly..)
anyways, now when i try to search that link , i could not find..
postgres sounds better than postgre...

see this...

http://www.postgresql.org/community/survey.33


Survey Results

The current results of our How do you pronounce 'PostgreSQL'? survey are:

Answer
Responses
Percentage
post-gres-q-l237945.168%
post-gres161130.587%
pahst-grey240.456%
pg-sequel500.949%
post-gree3506.645%
postgres-sequel57410.898%
p-g490.930%
database2304.367%
Total5267







From:Josh Kupershmidt <schmiddy@gmail.com>
To:Michael Wood <esiotrot@gmail.com>
Cc:pgsql-novice@postgresql.org
Date:03/09/2010 11:32 PM
Subject:Re: [NOVICE] Urgent help needed- alias name in update statement
Sent by:pgsql-novice-owner@postgresql.org




On Tue, Mar 9, 2010 at 10:42 AM, Michael Wood <esiotrot@gmail.com> wrote:
[snip]
> I don't know why the above doesn't work.  I've encountered something
> in the past which may be related:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
>    AND other = 123;
>
> This works, but what I want to do is the following:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND blah <> ''
>    AND other = 123;
>
> This does not work and I don't know why not.

This behavior is mandated by the SQL standard, I believe. I'm too lazy
to dig up the actual reference, but for instance
http://dev.mysql.com/doc/refman/5.0/en//problems-with-alias.html
claims:
   Standard SQL disallows references to column aliases in a WHERE clause.
   This restriction is imposed because when the WHERE clause is evaluated,
   the column value may not yet have been determined...

You could workaround by using a subquery like:

SELECT mysubq.blah FROM (
   SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
   FROM mytable
   WHERE something IS NOT NULL
   AND other = 123
)  AS mysubq
WHERE mysubq.blah <> '' ;

Josh

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice


=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

В списке pgsql-novice по дате отправления:

Предыдущее
От: Josh Kupershmidt
Дата:
Сообщение: Re: Urgent help needed- alias name in update statement
Следующее
От: "Machiel Richards"
Дата:
Сообщение: Slony vs Pgpool