Now, this *should* work. Unfortunately, it doesn't. I get an 'ORA-00904 invalid column name' error.
I've seen this many times. Such SELECTs are essentially correlated sub-SELECTs and are supposed to reference a column from one of the exterior tables. This type of SELECT only generates this error when 2 levels are involved in the sub-SELECT.
For example, this would work fine:
A.INDIVIDUAL_ID = I.INDIVIDUAL_ID
) MAXSTATECODE ,
It is the fact that the sub-SELECT contains 2 levels that causes the error. I've hit this problem before and have usually been able to re-write the query to get around the problem. It is also possible to do a really ugly re-write in this case as well (since there is a finite set of STATE_CD values)
However, I *really* don't want to do that if I don't have to.
I was hoping somebody out there might have some insight I am lacking on this one.
Thanks in advance,
(BTW - I can also move the sub-SELECT into the main query, but I am not interested in that solution. I want to solve the problem while maintaining a SELECT-clause sub-SELECT)