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 [EMAIL PROTECTED]) -- 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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
