Oracle Procedure to join two tables with latest status

Question

Please help me make an oracle stored procedure ; I have two tables

tblLead:

lead_id Name
1        x
2        y
3        z

tblTransaction:

Tran_id  lead_id   date          status

1          1       04/20/2010    call Later 
2          1       05/05/2010    confirmed

I want a result like

lead_id  Name   status

1         x     confirmed
2         y     not available !
3         z     not available ! 

Answer 1

It can be written in plain SQL as follows,

SELECT lead_id, name, NVL(status,'not available !')
FROM   ( 

SELECT tblLead.lead_id, tblLead.name, tblTransaction.status,
rank ( ) OVER (PARTITION BY tblTransaction.lead_id ORDER BY tblTransaction.datee DESC, tblTransaction.tran_id DESC) rank
FROM tblLead
LEFT JOIN tblTransaction ON tblLead.lead_id = tblTransaction.lead_id
)
WHERE  rank = 1
ORDER BY lead_id;

Or you may think of writing a view as follows,

CREATE VIEW trx_view AS 
------
------;

Personally I think stored procedure is not necessary for scenarios like this.

Answer 2

Use an outer join to the relevant rows of tblTransaction:

SQL> SELECT l.lead_id, l.NAME,
  2         CASE
  3            WHEN t.status IS NULL THEN
  4             'N/A'
  5            ELSE
  6             t.status
  7         END status
  8    FROM tbllead l
  9    LEFT JOIN (SELECT lead_id,
 10                      MAX(status) KEEP(DENSE_RANK FIRST 
 11                                       ORDER BY adate DESC) status
 12                 FROM tbltransaction
 13                GROUP BY lead_id) t ON l.lead_id = t.lead_id;

   LEAD_ID NAME STATUS
---------- ---- ----------
         1 x    confirmed
         2 y    N/A
         3 z    N/A

Alternatively you can use analytics:

SQL> SELECT lead_id, NAME, status
  2    FROM (SELECT l.lead_id, l.NAME,
  3                  CASE
  4                     WHEN t.status IS NULL THEN
  5                      'N/A'
  6                     ELSE
  7                      t.status
  8                  END status,
  9                  row_number()
 10                     over(PARTITION BY l.lead_id ORDER BY t.adate DESC) rn
 11             FROM tbllead l
 12             LEFT JOIN tbltransaction t ON l.lead_id = t.lead_id)
 13   WHERE rn = 1;

   LEAD_ID NAME STATUS
---------- ---- ----------
         1 x    confirmed
         2 y    N/A
         3 z    N/A