Firebird how to use IIF in ORDER BY with a subquery's column

Question

The following query doesn't work, because Firebird (2.1) won't recognize the subquery's column. How can I rewrite it to work?

SELECT I.InvoiceID,
  I.PayByDate,
  (SELECT FIRST 1 I2.PayByDate
  FROM Invoices I2
  WHERE I2.OriginalInvoiceID = I.InvoiceID
    AND I2.IsDraft < 1
  ORDER BY I2.InvoiceID DESC) AS NewPayByDate
FROM Invoices I
WHERE
  I.IsDraft < 1
ORDER BY
  IIF(NewPayByDate IS NULL,
    PayByDate,
    NewPayByDate),
  I.InvoiceRefNum

Answer 1

Use a subquery:

SELECT InvoiceID, PayByDate, NewPayByDate
FROM (SELECT I.*
             (SELECT FIRST 1 I2.PayByDate
              FROM Invoices I2
              WHERE I2.OriginalInvoiceID = I.InvoiceID AND
                    I2.IsDraft < 1
              ORDER BY I2.InvoiceID DESC
             ) AS NewPayByDate
      FROM Invoices I
      WHERE I.IsDraft < 1
     ) I
ORDER BY IIF(NewPayByDate IS NULL, PayByDate, NewPayByDate),
         I.InvoiceRefNum;

I would also recommend using COALESCE() in the ORDER BY:

ORDER BY COALESCE(NewPayByDate, PayByDate),
         I.InvoiceRefNum;