pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re: " Adding missing FROM-clause entry for table .... " problem.
Date: Fri, 21 Feb 2003 18:38:27 +0530
Message-ID: <200302211838.27756.mallah@trade-india.com> (raw)
In-Reply-To: <3E520C32.CD9C82FA@rodos.fzk.de>
References: <3E520C32.CD9C82FA@rodos.fzk.de>
Hmmm i forgot to follow up.
Thanks for pointing out the relevent Docs.
Regds
Mallah.
On Tuesday 18 February 2003 04:04 pm, Christoph Haller wrote:
> > We find that if we alias a tablename and refer to that tablename in
>
> where cluase instead of reffering
>
> > to the alias it produces wrond results.
> >
> > EG:
> > select to_char(a.generated, 'DD/Mon/YYYY' ) ,userid,email,descr from
>
> membership_invoice a join payment_classes using(
> payment_class)
>
> > join users using(userid) join membership_status using(userid) where
>
> membership_invoice.status='a' and granted is fa
> lse and
>
> > membership_invoice.last_reminder is null and current_date -
>
> date(a.generated) > 4 limit 10 ;
>
> > NOTICE: Adding missing FROM-clause entry for table
>
> "membership_invoice"
>
> > Where as merely rewriting the quer to use defined aliases gives the
>
> correct results.
>
> > select to_char(a.generated, 'DD/Mon/YYYY' ) ,userid,email,descr from
>
> membership_invoice a join payment_classes
>
> > using(payment_class) join users using(userid) join membership_status
>
> using(userid) where a.status='a' and granted i
> s
>
> > false and a.last_reminder is null and current_date - date(a.generated)
> > 4 ;
> >
> > Can Anyone please explain if its a BUG or problem in my understanding
>
> I think it's a problem in understanding. The documentation (7.2.1)
> states (as the NOTICE: does)
>
> 2.2.1.3. Table and Column Aliases
>
> A temporary name can be given to tables and complex table references to
> be used for references to the derived table in further
> processing. This is called a table alias.
>
> FROM table_reference AS alias
>
> Here, alias can be any regular identifier. The alias becomes the new
> name of the table reference for the current query -- it is no
> longer possible to refer to the table by the original name. Thus
>
> SELECT * FROM my_table AS m WHERE my_table.a > 5;
>
> is not valid SQL syntax. What will actually happen (this is a PostgreSQL
> extension to the standard) is that an implicit table
> reference is added to the FROM clause, so the query is processed as if
> it were written as
>
> SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Regds
Mallah
----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
view thread (12+ messages)
Message-ID: <200302211838.27756.mallah@trade-india.com> Permalink: ../200302211838.27756.mallah@trade-india.com/ Also on: postgresql.org/message-id/200302211838.27756.mallah@trade-india.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: mallah@trade-india.com, ch@rodos.fzk.de Subject: Re: " Adding missing FROM-clause entry for table .... " problem. In-Reply-To: <200302211838.27756.mallah@trade-india.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox