Using outer query ID in a subquery

I have a table with a list of invoices with there statuses and reported date, let’s say each date we have new report could change the invoice status from PENDING to PAID. I want subquery the first date an invoice was paid for each invoice. The table looks like this:

invoice_id	report_date	status
A1	08-26-2021	PAID
A2	08-26-2021	PENDING
A3	08-26-2021	PENDING
A4	08-26-2021	PENDING
A1	08-28-2021	PAID
A2	08-28-2021	PENDING
A3	08-28-2021	PAID
A4	08-28-2021	PENDING
A1	08-30-2021	PAID
A2	08-30-2021	PENDING
A3	08-30-2021	PAID
A4	08-30-2021	PENDING

I have a query like:

SELECT 
  invoice_id,
  report_date,
  status,
  CASE status
     WHEN 'PAID' THEN
        (SELECT report_date FROM invoices WHERE invoice_id = #INVOICE_REF_ID
         AND status = 'PAID' ORDER BY report_date LIMIT 1)
  END AS paid_date
FROM invoices

I expect something like:

invoice_id	report_date	status	paid_date
A1	08-26-2021	PAID	08-26-2021
A2	08-26-2021	PENDING
A3	08-26-2021	PENDING
A4	08-26-2021	PENDING
A1	08-28-2021	PAID	08-26-2021
A2	08-28-2021	PENDING
A3	08-28-2021	PAID	08-28-2021
A4	08-28-2021	PENDING
A1	08-30-2021	PAID	08-26-2021
A2	08-30-2021	PENDING
A3	08-30-2021	PAID	08-28-2021
A4	08-30-2021	PENDING

I need to know what should I replace #INVOICE_REF_ID so it references the invoice_id selected in the parent query.

Answer

If you include table aliases in your subquery, you would be able to achieve this. In the sample below I have assigned the alias main_inv to the outer query

SELECT 
  invoice_id,
  report_date,
  status,
  CASE status
     WHEN 'PAID' THEN
        (SELECT report_date FROM invoices WHERE invoice_id = main_inv.invoice_id
         AND status = 'PAID' ORDER BY report_date LIMIT 1)
  END AS paid_date
FROM invoices main_inv

Since you are using postgresql, you could also achieve this with a window function MIN eg

SELECT 
  invoice_id,
  report_date,
  status,
  CASE 
      WHEN status='PAID' THEN MIN(report_date) OVER (PARTITION BY invoice_id,status)
  END AS paid_date
FROM invoices 

Let me know if this works for you.