X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 05AEF474E44 for ; Tue, 18 Feb 2003 05:44:04 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 348B0474E42 for ; Tue, 18 Feb 2003 05:44:03 -0500 (EST) Received: from klaster.net (pa240.krakow.cvx.ppp.tpnet.pl [213.76.36.240]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id E4E9E2B876; Tue, 18 Feb 2003 11:34:53 +0100 (CET) Message-ID: <3E520E47.3080802@klaster.net> Date: Tue, 18 Feb 2003 11:43:19 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Rajesh Kumar Mallah Cc: pgsql-sql@postgresql.org Subject: Re: " Adding missing FROM-clause entry for table .... " problem. References: <200302181534.34822.mallah@trade-india.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/395 X-Sequence-Number: 11793 Rajesh Kumar Mallah wrote: > Hi , > > 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 false 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" > +-------------+--------+--------------------------+---------------+ > | to_char | userid | email | descr | > +-------------+--------+--------------------------+---------------+ > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > | 23/Nov/2002 | 34886 | pradeepk@trade-india.com | IID TradePass | > +-------------+--------+--------------------------+---------------+ > > 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 is > false and a.last_reminder is null and current_date - date(a.generated) > 4 ; > +-------------+--------+--------------------------------+------------------------+ > | to_char | userid | email | descr | > +-------------+--------+--------------------------------+------------------------+ > | 12/Feb/2003 | 125182 | blackandwhitetextile@yahoo.com | Mini Silver MemberShip | > | 13/Feb/2003 | 117512 | vaishnavipower@vsnl.net | Mini Silver MemberShip | > +-------------+--------+--------------------------------+------------------------+ > (2 rows) > > > Can Anyone please explain if its a BUG for problem in my > understanding > > > Regds > Mallah. It's not a bug. You have two choices: - remove alias "a" and use "membership_invoice." - use alias "a", but you have to change "membership_invoice." into "a." If you use alias, table is no longer available on it's own name. Regards, Tomasz Myrta