Re: " Adding missing FROM-clause entry for table .... " problem. - Mailing list pgsql-sql
From | Rajesh Kumar Mallah |
---|---|
Subject | Re: " Adding missing FROM-clause entry for table .... " problem. |
Date | |
Msg-id | 200302211838.27756.mallah@trade-india.com Whole thread Raw |
In response to | Re: " Adding missing FROM-clause entry for table .... " problem. (Christoph Haller <ch@rodos.fzk.de>) |
List | pgsql-sql |
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.