Here's the deal: I want one column of a resultset to contain a column-separated list of states that the person has addresses in.

For various reasons, I need to do this using a SELECT-clause sub-SELECT:

Code:
SELECT
	(
		SELECT
			MAX(STATE_1)||
			MAX(STATE_2)||
			MAX(STATE_3)||
			MAX(STATE_4)||
			MAX(STATE_5)||
			MAX(STATE_6)||
			MAX(STATE_7)||
			MAX(STATE_8)||
			MAX(STATE_9)||
			MAX(STATE_10)
			...
		FROM
		(
			SELECT
				DECODE(ROWNUM, 1,	 STATE_CD)	STATE_1,
				DECODE(ROWNUM, 2,', '||STATE_CD)	STATE_2,
				DECODE(ROWNUM, 3,', '||STATE_CD)	STATE_3,
				DECODE(ROWNUM, 4,', '||STATE_CD)	STATE_4,
				DECODE(ROWNUM, 5,', '||STATE_CD)	STATE_5,
				DECODE(ROWNUM, 6,', '||STATE_CD)	STATE_6,
				DECODE(ROWNUM, 7,', '||STATE_CD)	STATE_7,
				DECODE(ROWNUM, 8,', '||STATE_CD)	STATE_8,
				DECODE(ROWNUM, 9,', '||STATE_CD)	STATE_9,
				DECODE(ROWNUM,10,', '||STATE_CD)	STATE_10
				...
			FROM
				ADDRESS A
			WHERE
				A.INDIVIDUAL_ID	=	I.INDIVIDUAL_ID
		)
	)			STATELIST				,
	...
FROM
	INDIVIDUAL	I,
	...
WHERE
	...
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:

Code:
SELECT
	(
		SELECT
			MAX(STATE_CD)
		FROM
			ADDRESS A
		WHERE
			A.INDIVIDUAL_ID	=	I.INDIVIDUAL_ID
	)			MAXSTATECODE				,
	...
FROM
	INDIVIDUAL	I,
	...
WHERE
	...
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,

- Chris

(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)