Simpler way to add ad-hoc consecutive record numbers to queries and reports?

[nettings]

HI *!

I was faced with the challenge to generate a set of invoices from a database, where the invoice number had to be generated on-the-fly based on the record number. Other people are probably facing the same general problem: how to include consecutive numbers for each record into a report.
None of the “Counter” options in the Oracle Report Builder of LO 4.4.0.3 seemed to be doing what I needed (they counted the Details rows, not the records as such).
So here’s what I did. Please laugh at me and tell me there’s a simpler way…

1. Create a query qryInvoicesDistinct to join clients and items bought between :from_date and :to_date, output only client ID as DISTINCT values, so that I get one result record per client.
   SELECT DISTINCT “Clients”.“LastName”, “Clients”.“FirstName”, “Clients”.“ID” FROM “Purchases” “Purchases”, “Clients” “Clients” WHERE “Purchases”.“ClientID” = “Clients”.“ID” AND “Purchases”.“Date” >= :from_date AND “Purchases”.“Date” <= :to_date ORDER BY “Clients”.“LastName” ASC, “Clients”.“FirstName” ASC, “Clients”.“ID” ASC
2. Create a second query qryInvoicesDistinctNumbered that uses the query above, plus a second result field ROWNUM() (this might not be portable, but works for hsqldb).
   SELECT “ID”, ROWNUM( ) “RecordNumber” FROM “qryInvoicesDinstinct” “qryInvoicesDinstinct”
3. Create a final query qryInvoices that contains all the data required for the report, ordered by the same criteria as above (important!), then join it to qryInvoicesDistinct via ID to include the record number:
   Now we have an ad-hoc record number at our disposal to include anywhere in the final report.

It works, at least for databases that support ROWNUM(), but it feels like jumping through burning hoops for a task that should be straightforward. Are there any better options?

Thanks, Jörn