-
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)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|