pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomasz Myrta <jasiek@klaster.net>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: " Adding missing FROM-clause entry for table .... " problem.
Date: Tue, 18 Feb 2003 11:43:19 +0100
Message-ID: <3E520E47.3080802@klaster.net> (raw)
References: <200302181534.34822.mallah@trade-india.com>

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




view thread (12+ messages)  latest in thread

Message-ID: <3E520E47.3080802@klaster.net>
Permalink:  ../3E520E47.3080802@klaster.net/
Also on:    postgresql.org/message-id/3E520E47.3080802@klaster.net

 · 

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: jasiek@klaster.net, mallah@trade-india.com
  Subject: Re: " Adding missing FROM-clause entry for table .... " problem.
  In-Reply-To: <3E520E47.3080802@klaster.net>

* 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