Equivalent of SQL Server's "TOP 1" in Oracle (without using rownum or row_number())

Question

I have a CONTRACTINFO table which stores contract's discount code (the code can change from time to time, depending on the type of the contract or the period).

CREATE TABLE CONTRACTINFO
(
    ID CHAR(8),
    BASERECORD CHAR(1),
    DATE CHAR(8),
    DISCOUNTCODE CHAR(1)
)

Every month, we need to calculate the fee the customers have to pay based on paymentdate and discountcode.

CREATE TABLE PAYMENT
(
    CONTRACTID CHAR(8),
    TIME NUMBER(12),
    PAYMENTDATE CHAR(8)
)

The discount code is determined by getting the last record which has date < paymentdate from CONTRACTINFO table.

I have created a simple example to show the desired result (in yellow).

In SQL Server I can easily achieve this using below correlated subquery:

SELECT 
    PA.*,
    (SELECT TOP 1 DISCOUNTCODE 
     FROM CONTRACTINFO 
     WHERE ID = PA.CONTRACTID 
       AND DATE < PA.PAYMENTDATE 
     ORDER BY DATE DESC) AS DISCOUNTCODE 
FROM 
    PAYMENT PA
INNER JOIN 
    CONTRACTINFO CI ON PA.CONTRACTID  = CI.ID 
WHERE 
    CI.BASERECORD = 1 'ALWAYS GET INFORMATION FROM THE BASE RECORD

But in Oracle SQL I can't because it doesn't have a top 1 function.

I can't use rownum or row_number also because Oracle doesn't allow me to pass value from main query's column into nested sub query like this. (the below code will generate "column PA.PAYMENTDATE not found" error)

SELECT 
PA.*,
(
SELECT DISCOUNTCODE FROM 
   (SELECT * FROM CONTRACTINFO WHERE ID = PA.CONTRACTID AND DATE < PA.PAYMENTDATE ORDER BY DATE DESC) 
WHERE ROWNUM = 1
) 
AS DISCOUNTCODE 
FROM PAYMENT PA
INNER JOIN CONTRACTINFO CI 
ON PA.CONTRACTID  = CI.ID 
WHERE CI.BASERECORD = 1 'ALWAYS GET INFORMATION FROM THE BASE RECORD

Answer 1

Oracle does not support TOP 1, as you pointed out. You might be able to rewrite in Oracle while maintaining the correlated subquery, but the best option would probably be to remove that subquery, and instead just use the join you already making to handle the logic:

WITH cte AS (
    SELECT
        PA.*,
        COALESCE(CI.DISCOUNTCODE, 'NA') AS DISCOUNTCODE,
        ROW_NUMBER() OVER (PARTITION BY CI.ID ORDER BY CI.DATE DESC) rn
    FROM PAYMENT PA
    LEFT JOIN CONTRACTINFO CI
        ON PA.CONTRACTID = CI.ID AND
           CI.DATE < PA.PAYMENTDATE 
    WHERE
        CI.BASERECORD = 1
)

SELECT CONTRACTID, TIME, PAYMENTDATE, DISCOUNTCODE
FROM cte
WHERE rn = 1;