Oracle 8i - unable to reference outer table from subquery

Question

I have 3 such tables in an Oracle 8i dB

Table: NCM_HEADER@ncmsp H

NCR_KEY
 --------
 4412717
 4412718
 4412719 

Table: NCM_DISCREPANCY@ncmsp D

 NCR_Key DISCREPANCY_NUMBER STATUS
 ---------------------------------
 4412717    1               'OPEN'
 4412717    2               'OPEN'
 4412718    1               'CLOSED'

Table: NCM_SIGNOFF@ncmsp t

NCR_Key DISCREPANCY_NUMBER GROUP_NAME SEQUENCE_NUMBER
------------------------------------------------------
4412717    1                GROUP3      3
4412717    1                GROUP2      2
4412717    1                GROUP1      1
4412717    2                GROUP4      1
4412718    1                GROUP5      1

I am looking for the below query to return a value of "GROUP3"

Method

1. Filter the D.NCR_KEY and D.DISCREPANCY_NUMBER
2. Subquery also is meant to filter for these same numbers and extract all 3 values for the NCR_KEY specified
3. Subquery orders by SEQUENCE_NUMBER descending
4. Outer query only selects the top 1

For some reason, it's not recognizing the D table and is failing on D.DISCREPANCY_NUMBER and D.NCY_KEY in the subquery with error message:

"D"."DISCREPANCY_NUMBER": invalid identifier

The below query is part of a larger query. How do I return 'GROUP3'?

SELECT
CASE WHEN D.DISCREPANCY_STATUS = 'OPEN' 
    THEN (SELECT *
            FROM (SELECT
                t.GROUP_NAME
                FROM NCM_SIGNOFF@ncmsp t
                WHERE t.NCR_KEY = D.NCR_KEY AND t.DISCREPANCY_NUMBER = D.DISCREPANCY_NUMBER --error point
                ORDER BY t.SEQUENCE_NUMBER DESC)
            WHERE ROWNUM = 1)
    ELSE NULL
    END AS "Current Group"
    
FROM NCM_HEADER@ncmsp H
LEFT JOIN NCM_DISCREPANCY@ncmsp D ON D.NCR_KEY = H.NCR_KEY
WHERE D.NCR_KEY = 4412717 AND D.DISCREPANCY_NUMBER = 1

Answer 1

Oracle 8i? Oh my... Anyway, in the days before we could push things down into multiple nested levels, a common hack was to use MAX and bring the ordering column (SEQUENCE_NUMBER) into the expression to mimic the effect of sorting.

Thus

SELECT
CASE WHEN D.DISCREPANCY_STATUS = 'OPEN' 
    THEN (SELECT substr(max(lpad(t.SEQUENCE_NUMBER,10,'0')||t.GROUP_NAME,11)
          FROM NCM_SIGNOFF@ncmsp t
          WHERE t.NCR_KEY = D.NCR_KEY AND t.DISCREPANCY_NUMBER = D.DISCREPANCY_NUMBER 
         )
    END AS "Current Group"
FROM NCM_HEADER@ncmsp H,
NCM_DISCREPANCY@ncmsp D 
WHERE D.NCR_KEY = 4412717 
AND D.DISCREPANCY_NUMBER = 1
AND D.NCR_KEY = H.NCR_KEY(+)

The first 10 chars are the sequence number, so we SUBSTR from 11 onwards to get the value of the group name that pertains to this highest sequence number.

But given the number of security patches there have been in database versions since 8i all the way up to 19c, getting queries to work pretty much pales into insignificance compared to getting hacked and your company put out of business. Maybe that's an argument you can take to your CIO to get off 8i.