Does QODBC support inline views?

Example:

select cust.FullName
from customer cust
left outer join (SELECT CustomerRefListID, max(TxnDate) AS maxDate
                          FROM invoice GROUP BY CustomerRefListID) inv2
on inv2.CustomerRefListID = cust.ListId

QODBC currently only supports nested sub-selects & "exists", for example:

SELECT * FROM EMPLOYEE
     WHERE NAME IN (SELECT NAME FROM EMPLOYEE)

SELECT FullName FROM Customer cust
     WHERE EXISTS
     (SELECT CustomerRefListID, max(TxnDate) AS maxDate
      FROM invoice GROUP BY CustomerRefListID)

Most of the customer details are already within the invoice. So most of the time you don't need to link tables in QODBC as most of the header details are always embedded in the transactions. The headers in QODBC act as placeholders. But if you wanted to see, say the JobEndDate, you can link the tables like this:

SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
InvoiceLine.TxnDate, InvoiceLine.InvoiceLineDesc,
InvoiceLine.InvoiceLineQuantity, Customer.Balance, Customer.JobEndDate
FROM Customer Customer, InvoiceLine InvoiceLine
WHERE InvoiceLine.CustomerRefListID = Customer.ListID

or do:

SELECT FullName, JobEndDate FROM Customer cust
     WHERE EXISTS
     (SELECT CustomerRefListID, max(TxnDate) AS maxDate
      FROM invoice GROUP BY CustomerRefListID)

I know you are really trying to help and I don't want to belabor the point, but I don't see how to get this simple task done.  The query provided will only select customers that have invoices, which is not what I need, plus it returns none of the invoice data for the most current invoice for each customer.

The other solution is to link to the tables, but the query I wrote to do this (see first message) will not run because in-line views are not supported.  Sounds like I am at an impass here.  This is the first task I have tried since downloading the trial version of the QODDB driver.

Thank you.

That's right, if you do a GROUP BY the details are lost (but you started down this road .. not me), but this will do what you want:

SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
InvoiceLine.TxnDate, InvoiceLine.InvoiceLineDesc,
InvoiceLine.InvoiceLineQuantity, Customer.Balance, Customer.JobEndDate
FROM Customer Customer, InvoiceLine InvoiceLine
WHERE InvoiceLine.CustomerRefListID = Customer.ListID

Ok, so the invoice line file only contains invoice data for the most current invoice, who knew.  We thought it contained data for all the invoices for that customer.  I only wanted the most current invoice, and that is why I had to do the group by, to get the max transaction date for all the invoices for that customer.  We didn't know that about the invoice line table.

Thank you.

No,

Invoice:                      Is the Main Invoice Table and contains a
                                   HEADER rowset for every invoice.
InvoiceLine:               Provides line item detail rowsets for each customer invoice;
                                   contains all the information included in the Main Invoice Table
InvoiceLinkedTxn:     Payments, Credit Memos, Deposit Line Items; does
                                   NOT include reimbursements, transfers from SO or Estimates

Ok, I will just assume that it is not possible to do what I need with the QODBC driver, or at least neither of us can figure out how to do it.  We could have saved a lot of time by just saying that in the beginning.  However, the driver is saving me billions of hours of hard work, and I do very much appreciate what it does do.  I am sure I will purchase the license for it.

Thank you.