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
falseand
> 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
