CODEWITHSUNDEEP
sqloracle-databaseoracle11gcorrelated-subquery
EagerToLearn
EagerToLearn

Reputation: 675

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

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

enter image description here

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

Upvotes: 0

Views: 3614

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Oracle has fetch first instead of top n, so the equivalent would be:

select pa.*
     , ( select discountcode
         from   contractinfo
         where  id = pa.contractid
         and    contractdate < pa.paymentdate
         order  by contractdate desc fetch first row only ) as discountcode
from   payment pa
       join contractinfo ci
            on  pa.contractid = ci.id
where  ci.baserecord = 1;

I had to rename DATE to CONTRACTDATE because DATE is a SQL keyword. Also, although the char type is provided for ANSI completeness, it is not generally a good idea to use it because blank-padding is a rather pointless feature that wastes space and leads to bugs.

I would probably start with something like this:

create table contracts
( id            integer constraint contract_pk primary key
, baserecord    integer not null
, contractdate  date not null
, discountcode  varchar2(1) );

create table payments
( contractid    references contracts
, paymentseq    number(12)
, paymentdate   date default on null sysdate );

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

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;

Upvotes: 3

Related Questions