Cluster inside subquery inside subquery

Question

It is necessary in the subquery inside the subquery inside the subquery to be correlated with the field value with the external table. Request such:

select ( select CATEG from (select CATEG from SHIFTS where TABNOM = STATE.TABNOM order by TABNOM, DATEPR desc) where ROWNUM = 1 ) from STATE

Here I am trying to get a category (CATEG) from the most recent by date (DATEPR) record for this personnel number (TABNOM) of an employee from the movement table (SHIFTS), for all records from the staffing table (STATE).

But I get the error:

Oracle.DataAccess.Client.OracleException: ORA-00904: "STATE". "TABNOM": invalid identifier

It needs to be done without join-s, only by subqueries, since the entire request is partially formed by an application that cannot be affected.

In addition to the 2 proposed solutions for Oracle 12c, you can use FETCH FIRST 1 ROWS ONLY .
For any version of oracle, it may be convenient to make a function (create function ...) and forget about the subquery altogether, the request itself will end up like select getLastCategory(tabnom) from state

Dmitriy Dmitriy 2.574 3 eleven 23 · Accepted Answer · 2018-02-13T16:24:19

You can reduce the level of nesting with this clever design:

 select (select max(categ) keep (dense_rank first order by datepr desc) categ from shifts where tabnom = state.tabnom) from state

The expression max(categ) keep (dense_rank first order by datepr desc) means "sort the table by descending datepr field, take minimal dense_rank (that is, the last date value), and take the maximum value of the categ field from them".

um, I wonder you decided that the maximum category will be the last - how so?
)) on Oracle 2, the nested subqueries seem to be normal, and in the norms of the databases, one nested
@EugeneBartosh Dmitry was not mistaken; when using the phrase KEEP, the aggregate function itself does not matter, you can write at least AVG, only 1 value will get inside the function, selected in the sort order specified for KEEP.
And 2 subqueries, of course, are not a problem until you need to refer from the innermost to the outermost
If you or the author of the question like another aggregate function - use it.
The main problem here is that it is necessary to use the aggregate function in this design.
For this case, you can use the noagr self- noagr function to noagr .

jurden jurden 81 five · Answer 2 · 2018-02-13T15:52:38

Without data is difficult, but try this:

 select ( select distinct First_Value(h.Categ) over (partition by h.Tabnom order by h.Datepr desc) from SHIFTS h where h.TabNom = s.Tabnom) from State s

Answer 3 · 2018-02-13T16:08:42

I remembered from Hotheads 2 - "now we are building a team to save those who went to save those, in order to help out those who came to help those who were captured by the enemies" :)

The solution to the problem is to use synonyms, in particular, it is necessary for subqueries in FROM, I also advise you to use fields for fields, I advise you to contact fields only through TABLE.FIELD - it saves a lot of time when requests start to get complicated, and especially when they are present. subqueries. I do not know which field from which table you have, so arrange yourself. Also in your case, you need to use LIMIT 1, thanks to this you can get rid of the second subquery and make the query compatible with SQL ANSI. TABNOM in order by is nonsense, it’s as if you offered 100 Ivanovs to clean themselves in alphabetical order by last name. In the final form:

  select (select SHIFTS.CATEG from SHIFTS where SHIFTS.TABNOM = STATE.TABNOM order by SHIFTS.DATEPR desc LIMIT 1 ) as CATEG from STATE

PS Well, if Oracle then tada so, has already forgotten about this bridge, it eventually ceased to distinguish it from PostgreSQL :-)

 select ( select TAB1.CATEG as CAT1 from ( select SHIFTS.CATEG as CAT2 from SHIFTS where SHIFTS.TABNOM = STATE.TABNOM order by SHIFTS.DATEPR desc) as TAB1 where ROWNUM = 1 ) as CATEG from STATE

There is no LIMIT in it (just the condition ROWNUM = 1 is applied instead of it).
And unfortunately, ANSI compliant queries are not compatible with Oracle, up to version 9, even the word JOIN was not there ... everything was decided differently.
Before writing a response on a specific database, always check whether the written is compatible
Oracle does not allow access from the correlated subquery to the table fields by more than 1 nesting level up.
horrors, how good it is that I have already forgotten all this :-))) sorry then for confusion, well, I am not an oralist, therefore I didn’t know Shmagl))

Cluster inside subquery inside subquery

3 answers 3

More articles: