-
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)
-
Well, I've slipped onto the second page with no responses, so I'm just pinging my own post.
Any ideas out there?
- Chris
-
Chris, your question has kept me bussy for at least 2 hours two nights ago, however I can not give you any usefull advice, only some of my observations.
First of all, you are wrong at saying "This *should* work" - it *can't* work. The reason is not in 2-levels of nesting as you assume, the reason is in the fact that you are trying to reference outer query's column from the *inline view*.
In your second example (the one that is working) you are referencing an outer query from sub-select that is positioned in the "column list " part of the query (between SELECT and FROM) of the outer query. This is perfectly legal and logical. But in your initial example (the one that is not working) you are trying to reference an outer query from an inline view query, that is the sub-query in the FROM clause. Now that is not permited, you can never reference an outer query's column from an inline view. Never. The fact that your inline view lies inside a sub-select in your "column list" does not change that fact.
So I gues you won't be able to overcome this without actually rewriting your query. But I must say your logic in this attempt was realy impressive - if it would have worked it would be an exceptional solution, definitelly much more efficient than any other solution. However those "other sollutions" at least work.... :D
-
Jurij,
Thank you for your reply - insightful as always.
First, just to clarify, my reference to '2 levels of nesting' is the same as 'referencing the outer column in an in-line view'. I just look at it a little differently :)
You say that "that is not permited, you can never reference an outer query's column from an inline view. Never." This certainly seems to be what I have found. My question is : Why?
Firstly because it seems so obvious to me that it *should* work and secondly because you seem so positive that it *can't* work. Does it specifically say this somewhere in the documentation that I missed?
My reasoning puts correlated sub-selects on par with binds. Basically, for each row in the outer resultset, run this query and use the value of the current row for the 'bind' variable.
If I changed my non-working example from I.INDIVIDUAL_ID to :INDIVIDUAL_ID, it becomes a legal statement. Of course I get the wrong results, but following my reasoning, if they can see the bind down 2 levels why can't they see the correlating column down 2 levels and handle it accordingly?
I know, of course, that there's nothing you or I can do to change this, but it is *soooo* frustrating. I have been stonewalled by this 'error' (in my opinion;)) on enough occasions that I am really getting p****d off!
Sorry, venting over :D
Thanks,
- Chris
