|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Invoice line items with quantity of zero |
| Author |
Message |
|
|
| Posted : 2007-01-27 08:41:11 |
The following SELECT is intended to find all InvoiceLines from pending invoices as long as they have NON-ZERO quantities:
SELECT Inv.CustomerRefFullName, Inv.RefNumber, Inv.TxnDate, Inv.Memo, InvLine.InvoiceLineSeqNo, InvLine.InvoiceLineItemRefFullName, InvLine.InvoiceLineQuantity, Item.CustomFieldLicenses_per_item, InvLine.InvoiceLineServiceDate FROM Customer Cust, Invoice Inv, InvoiceLine InvLine, ItemNonInventory Item WHERE Cust.ListID = Inv.CustomerRefListID AND InvLine.TxnID = Inv.TxnID AND Item.ListID = InvLine.InvoiceLineItemRefListID AND ((Inv.IsPending=true) AND (InvLine.InvoiceLineQuantity>0))
For some reason, this does NOT "knock out" the lines with quantities of .00000:
Curiously, I WAS able to exclude the zeros using:
InvLine.InvoiceLineQuantity>0.0000 |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-27 12:35:40 |
I have also found that some nested parameters less than 8 parameters don't work correctly when passed through to the SQLite backend used by the QODBC optimized. This query returned 6 lines:
SELECT TxnDate as Date, CustomerRefFullName as Name, RefNumber as Ref,
InvoiceLineItemRefFullName as Service, InvoiceLineQuantity as Qty,
InvoiceLineRate as Rate, InvoiceLineAmount as Amount
FROM InvoiceLine Where CustomerRefFullName='Cook, Brian:Kitchen'
and (InvoiceLineItemRefFullName='Framing'or InvoiceLineItemRefFullName='Installation')
and InvoiceLineQuantity > 8 and TxnDate >= {d'2007-10-01'}
and included lines where InvoiceLineQuantity was less than quantity 8.
However if I use Select * only returns with 3 lines (and is correct):
SELECT * FROM InvoiceLine Where CustomerRefFullName='Cook, Brian:Kitchen'
and (InvoiceLineItemRefFullName='Framing'or InvoiceLineItemRefFullName='Installation')
and InvoiceLineQuantity > 8 and TxnDate >= {d'2007-10-01'}
and if I run the query unoptimized directly against QuickBooks it also returns 3 lines (or course):
SELECT TxnDate as Date, CustomerRefFullName as Name, RefNumber as Ref,
InvoiceLineItemRefFullName as Service, InvoiceLineQuantity as Qty,
InvoiceLineRate as Rate, InvoiceLineAmount as Amount
FROM InvoiceLine unoptimized Where CustomerRefFullName='Cook, Brian:Kitchen'
and (InvoiceLineItemRefFullName='Framing'or InvoiceLineItemRefFullName='Installation')
and InvoiceLineQuantity > 8 and TxnDate >= {d'2007-10-01'}
There's a typing problem when using 0 or 8 instead of 0.0000 or 8.0000 and I have reported the bug.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-04 09:17:09 |
|
|
|
|