I have a query that looks somewhat like this:
select
...,
my_view.alias_name
from
tbl1 join
tbl2 on
tbl1.key = tbl2.key join
tbl3 on
tbl3.key = tbl3.key join
(
select
...,
(max(...) keep (...)) alias_name
from
...
) my_view on
tbl3.key = my_view.key
where
...;
It doesn't work because the alias_name
isn't set (maintaining the name (max(...) keep (...))
which I don't know if its possible to reference in the select my_view.name_or_alias
) when I do it this way joining the inline view to the tables, but strangely enough it does work when I join the tables after the inline view instead.
select
...,
my_view.alias_name
from
(
select
...,
(max(...) keep (...)) alias_name
from
...
) my_view join
tbl3 on
my_view.key = tbl3.key join
tbl2 on
tbl3.key = tbl2.key join
tbl1 on
tbl2.key = tbl1.key
where
...;
Is there any explanation or documentation that talks about this or is it undefined/random behavior? Any way to make it work with the inline view joining the tables? I couldn't find any information about this.
The problem wasn't with Oracle nor the query after all but actually with Microsoft Query itself which was the program that was throwing the error.
It's old and I wish I didn't had to work with it...
Both of the following simplified queries work fine on Excel VBA but the second query doesn't on MS Query.