Description:
Sometimes a having clause does not accept aliased column names with quotes around them. For example, this statement will return no rows, but generates no errors:
=== Begin Query ===
select
a.c_uid as 'Account',
c_balance as 'Balance',
sum(
case when c_to_account=a.c_uid then c_amount else 0 end
)
+
sum(
case when c_from_account=a.c_uid then -c_amount else 0 end
)
- c_balance as 'Discrepancy'
from t_account as a, t_transaction as t
where
c_to_account=a.c_uid
or c_from_account=a.c_uid
group by a.c_uid
having 'Discrepancy' <> 0
order by 'Discrepancy', 'Account';
=== End Query ===
The same query, with the having line changed to
having Discrepancy <> 0
will succeed.
How to repeat:
Run the query above. That query assumes the following table definitions (I would be very happy to provide sample data, even a whole sample database... just email me, bps7j at yahoo dot c o m):
create table t_account (
c_uid int unsigned not null auto_increment,
c_owner int unsigned not null default 1, -- root
c_creator int unsigned not null default 1, -- root
c_group int unsigned not null default 3, -- treasurer
c_unixperms char(9) not null default '111111100', -- nonstandard!
c_created_date datetime not null,
c_last_modified timestamp not null,
c_status int unsigned not null default 1,
c_description char(100), -- Short descr. of the account
c_balance decimal(6,2) not null,
index (c_status),
index (c_balance),
primary key (c_uid)
) type=MyISAM;
create table t_transaction (
c_uid int unsigned not null auto_increment,
c_owner int unsigned not null default 1, -- root
c_creator int unsigned not null default 1, -- root
c_group int unsigned not null default 3, -- treasurer
c_unixperms char(9) not null default '111110100',
c_created_date datetime not null,
c_last_modified timestamp not null,
c_status int unsigned not null default 1,
c_category int unsigned not null, -- > t_transaction_category
c_from_account int unsigned not null, -- > t_account
c_to_account int unsigned not null, -- > t_account
c_amount decimal(6,2) not null,
c_relates_to char(30), -- table the object is stored in
c_related_uid int unsigned not null, -- foreign key to?
c_comment char(255),
index (c_relates_to),
index (c_related_uid),
index (c_from_account),
index (c_to_account),
index (c_category),
primary key (c_uid)
) type=MyISAM;
Suggested fix:
No suggestions