Pass id to inner SELECT of nested query with ORDER BY and ROWNUM

Question

I am facing a similar problem, I had a couple of months ago: I need to return a single value from a subquery that may or may not return a list of values. Thus, I apply ORDER BY first and select the topmost entry with WHERE ROWNUM=1 then (Yep, I'm on Oracle).

The problem is, the required ID in the inner query is unknown due to nesting.

Last time, I was adviced to use Oracles analytic functions and apply MIN()/MAX(). This time, however, I'm selecting a currency symbol (VARCHAR) and cannot utilize these functions.

Any help is greatly appreciated. This is the relevant part of my query:

SELECT
  MyOtherColumn,

  (SELECT CurrencySymbol FROM (
     SELECT DISTINCT P2.ID, P2.VALIDFROMDATE, CURR.CURRENCYSYMBOL AS CurrencySymbol FROM MY_ORDER_TABLEPOS OP2
     JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
     JOIN MY_PRICE_TABLE P2 ON OP2.FK_CONCRETARTICLE=P2.FK_ARTICLE
     JOIN MY_CURRENCY_TABLE CURR ON P2.FK_CURRENCY=CURR.ID
     WHERE OP2.FK_ORDER=O.ID AND P2.ID=( -- This fails, since O.ID is unknown in inner query!
       -- Determine current price for article
       SELECT MAX(P3.ID) KEEP (DENSE_RANK FIRST ORDER BY P3.MINTOORDER DESC, P3.VALIDFROMDATE DESC) CurrentPriceId
       FROM MY_PRICE_TABLE P3
       WHERE P3.FK_ARTICLE=OP2.FK_CONCRETARTICLE
       AND P3.VALIDFROMDATE<=SYSDATE
       AND P3.MINTOORDER<=OP2.AMOUNT
     )
     ORDER BY P2.VALIDFROMDATE DESC
  )
  WHERE ROWNUM=1) CurrencySymbol

FROM ...
WHERE O.ID=786;

Adding an ID to the inner query

SELECT DISTINCT P2.ID, P2.VALIDFROMDATE, CURR.CURRENCYSYMBOL AS CurrencySymbol FROM MY_ORDER_TABLEPOS OP2
JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
JOIN MY_PRICE_TABLE P2 ON OP2.FK_CONCRETARTICLE=P2.FK_ARTICLE
JOIN MY_CURRENCY_TABLE CURR ON P2.FK_CURRENCY=CURR.ID
WHERE OP2.FK_ORDER=786 AND P2.ID=( -- ID was set statically here
 -- Determine current price for article
 SELECT MAX(P3.ID) KEEP (DENSE_RANK FIRST ORDER BY P3.MINTOORDER DESC, P3.VALIDFROMDATE DESC) CurrentPriceId
 FROM MY_PRICE_TABLE P3
 WHERE P3.FK_ARTICLE=OP2.FK_CONCRETARTICLE
 AND P3.VALIDFROMDATE<=SYSDATE
 AND P3.MINTOORDER<=OP2.AMOUNT
)
ORDER BY P2.VALIDFROMDATE DESC;

gets me this result:

ID     VALIDFROMDATE                  CURRENCYSYMBOL
597    18.11.14 00:00:00,000000000    $
556    10.11.14 10:54:05,973000000    €
552    10.11.14 10:44:25,719000000    €
258    15.10.14 10:12:01,670000000    €

My goal is to return the CURRENCYSYMBOL of the topmost entry (here: $).

So, my question is: How can I pass the order ID to the nested query or how can I rewrite the query such that it meets my needs? I know the query is overly complex, my apologies for that. :)

Answer 1

  (SELECT CurrencySymbol FROM (
     SELECT OP2.FK_ORDER, CURR.CURRENCYSYMBOL AS CurrencySymbol,
            ROW_NUMBER() OVER(PARTITION BY OP2.FK_ORDER ORDER BY P2.VALIDFROMDATE DESC) RW
     FROM MY_ORDER_TABLEPOS OP2
         JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
         JOIN MY_PRICE_TABLE P2 ON OP2.FK_CONCRETARTICLE=P2.FK_ARTICLE
         JOIN MY_CURRENCY_TABLE CURR ON P2.FK_CURRENCY=CURR.ID
     WHERE P2.ID=(
       -- Determine current price for article
       SELECT MAX(P3.ID) KEEP (DENSE_RANK FIRST ORDER BY P3.MINTOORDER DESC, P3.VALIDFROMDATE DESC) CurrentPriceId
       FROM MY_PRICE_TABLE P3
       WHERE P3.FK_ARTICLE=OP2.FK_CONCRETARTICLE
       AND P3.VALIDFROMDATE<=SYSDATE
       AND P3.MINTOORDER<=OP2.AMOUNT
     )
  )
  WHERE FK_ORDER = O.ID AND RW=1) CurrencySymbol