0

I understand from this thread that Oracle does not allow ORDER BY in Subqueries, but I have a legitimate requirement that I need to implement: when creating a View, one of the columns is a value grabbed from the 1st result of a sorted sub-Select. It must be this way. How can I re-write this?

      CASE
           WHEN ... /* some condition */
           THEN
               (SELECT training_director_ned_id
                  FROM docs_vw docs
                 WHERE     docs.participating_org_active_flag = 'Y'
                       AND ts.nihsac LIKE
                               (docs.nihsac || '%') AND ROWNUM=1 ORDER BY docs.nihsac ASC)

The table DOCS_VW docs has a column called nihsac where values can be e.g.

HNC1
HNC1D
HNC1T
HNC2
HNC3

There is another table called ts being matched against this, with its own nihsac. Suppose ts.nihsac = HNC1 is being matched. I need to restrict the docs_vw rows to the 1st one sorted alphabetically, to avoid multiple results for a single cell within this View. Otherwise, there would be an error "Multiple rows returned for a subquery where a single row is expected."

gene b.
  • 10,512
  • 21
  • 115
  • 227
  • i am sure that is not only the order by that is wrong with your query, it should only return one value, so make a [mre] so thatwe can udnerstand your problem better – nbk Aug 12 '22 at 19:20
  • 1
    It's the only problem, you can be assured the query is correct and it runs properly. This is verified. – gene b. Aug 12 '22 at 19:20
  • oracle and its compiler can't know that, and so always show an error. so read up why always a [mre] should be provided https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – nbk Aug 12 '22 at 19:22
  • 1
    @nkb `i am sure that is not only the order by that is wrong with your query,` Here you are: [db<>fiddel demo](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=8b26907a78774242cf6f0f7896d901c6) – Lukasz Szozda Aug 12 '22 at 19:40
  • 2
    "the query is correct and it runs properly" - but if you ran the subquery on its own you would get an indeterminate single row because the rownum filter will be applied before the ordering. You would need another level of subquery (which could cause problems with correlation) - [demo added to previous fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=203aba2c3d673e0aed9a0a347313b67d); or the equivalent with `fetch`, as Lukasz showed. – Alex Poole Aug 12 '22 at 20:03
  • 1
    Does this answer your question? [How do I limit the number of rows returned by an Oracle query after ordering?](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) – astentx Aug 12 '22 at 20:50

1 Answers1

1

Query:

(SELECT training_director_ned_id
FROM docs_vw docs
WHERE docs.participating_org_active_flag = 'Y'
  AND ts.nihsac LIKE (docs.nihsac || '%') AND ROWNUM=1 
ORDER BY docs.nihsac ASC)

could be rewritten as:

(SELECT training_director_ned_id
FROM docs_vw docs
WHERE docs.participating_org_active_flag = 'Y'
  AND ts.nihsac LIKE  (docs.nihsac || '%')
ORDER BY docs.nihsac ASC
FETCH FIRST 1 ROW ONLY
)

db<>fiddle demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275