How to work around Oracle's ORDER BY not being allowed in Sub-Select Query ("missing right-CodePudding

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."

CodePudding user response：

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