appi appi - 6 months ago 80
SQL Question

(or SLV is undefined) Informix data base

Can anyone help me out here to find out the error in this small query.

select count(txno) as c1, rxno from mrgrxtxt
where c1>1
group by rxno;


error:
[Error Code: -217, SQL State: IX000] Column (c1) not found in any table in the query (or SLV is undefined).


If I comment out the WHERE clause (
where c1 >1
), it executes fine.

Answer

Or do the GROUP BY part in a derived table:

select c1, rxno
from
(
    select count(txno) as c1, rxno 
    from mrgrxtxt 
    group by rxno
)
where c1 > 1;

Very convenient when more complex aggregations. (Less typing, and smaller risk of errors when duplicating or adjusting expressions.)