<div>
I am not Informix inclined; however, from a straight SQL Syntax point of view, the:<br />
max(casact.activity_date)<br />
<br />
Does NOT automatically retain column name of activity_date. &nbsp;This is an expression, so you are going to get an untitled column or Expr1 or something to that effect. &nbsp;Then in your outer query you are wanting to call by name activity_date, so you will need to do something like this:<br />
max(casact.activity_date) AS activity_date<br />
<br />
That will alias the expression back to a column name of activity_date.
</div>


I am not Informix inclined; however, from a straight SQL Syntax point of view, the:
max(casact.activity_date)

Does NOT automatically retain column name of activity_date.  This is an expression, so you are going to get an untitled column or Expr1 or something to that effect.  Then in your outer query you are wanting to call by name activity_date, so you will need to do something like this:
max(casact.activity_date) AS activity_date

That will alias the expression back to a column name of activity_date.

<div>
Thanks for the answers, I will have a look at what you suggest
</div>


Thanks for the answers, I will have a look at what you suggest

<div>
<div class="content wysiwyg-content">
I am trying to use a derived table as part of a query in informix. I am tring to show the maximum date from it.<br />
<br />
I keep getting error messages, which are not helpful i.e. Unknown error message number '-201'<br />
<br />
select a.activity_date<br />
from<br />
(select casact.caseid, max(casact.activity_date)<br />
from arista01:student1.casact casact <br />
group by casact.caseid) a<br />
<br />
Does anybody know the correct syntax for the above query?<br />
<br />
Thanks
</div>
</div>


I am trying to use a derived table as part of a query in informix. I am tring to show the maximum date from it.

I keep getting error messages, which are not helpful i.e. Unknown error message number '-201'

select a.activity_date
from
(select casact.caseid, max(casact.activity_date)
from arista01:student1.casact casact 
group by casact.caseid) a

Does anybody know the correct syntax for the above query?

Thanks

Derived Table Problem

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.