X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8556947580B for ; Tue, 18 Feb 2003 05:04:07 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 7BB11475458 for ; Tue, 18 Feb 2003 05:04:03 -0500 (EST) Received: (qmail 3496 invoked from network); 18 Feb 2003 10:01:49 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 18 Feb 2003 10:01:49 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: pgsql-sql@postgresql.org Subject: " Adding missing FROM-clause entry for table .... " problem. Date: Tue, 18 Feb 2003 15:34:34 +0530 User-Agent: KMail/1.5 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200302181534.34822.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/393 X-Sequence-Number: 11791 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. -- 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.