Selecting last record in plsql-CodePudding

Hello I need to select last record in a table(bill_account_billcycle). After finding it I need to take bill_date column date from last record and need implement inside my cursors' query. I read rownum max etc but couldnt succeed to implement. I am open to advices. I am going to add a screenshoot of one bill_account record to there and my scripts. thank you from now.

    CURSOR c1 IS
SELECT DISTINCT intl_prod_id
      FROM apld_bill_rt abr,
           acct_bill ab,
           bill_acct_billcycle bab
      WHERE abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          AND ab.bill_date = bab.bill_date IN (select );-- I should do something here.

CodePudding user response：

From Oracle 12, you can use ORDER BY and then FETCH FIRST:

CURSOR c1 IS
  SELECT DISTINCT
         intl_prod_id
  FROM   apld_bill_rt abr
         INNER JOIN acct_bill ab
         ON (abr.acct_bill_id = ab.acct_bill_id)
         INNER JOIN bill_acct_billcycle bab
         ON (ab.bill_date = bab.bill_date)
  WHERE  abr.CHRG_TP = 'INSTALLMENT'
  ORDER BY bab.bill_date DESC
  FETCH FIRST ROW ONLY;

If you want multiple rows with the latest date than use FETCH FIRST ROW WITH TIES.

CodePudding user response：

To me, it looks like

CURSOR c1 IS
SELECT DISTINCT intl_prod_id
  FROM apld_bill_rt abr
       JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
 WHERE     ab.bill_date = (SELECT MAX (bab.bill_date)
                             FROM bill_account_billcycle bab)
       --
       AND abr.chrg_tp = 'INSTALLMENT'
       AND abr.tax_catg_id = 'NOTAX'

i.e. remove bill_account_billcycle table from cursor's FROM clause; select the last date using a subquery.