I'm wondering how to write the SQL for multiple WHERE functions for ODBC. I'm sure it is a simple problem, but I've written it in several different ways but haven't had any success. My SQL is as follows:

select ShipDate, CustomerRefFullName,ShipAddressCity,PONumber,SalesOrderLineItemRefFullName,
SalesOrderLineQuantity,SalesOrderLineDesc,ShipMethodRefFullName,TemplateRefFullName,
IsFullyInvoiced,IsManuallyClosed,RefNumber from salesorderLine nosync
WHERE Txndate >= {d '2006-05-01'} AND isfullyinvoiced=yes

It gives me the error message: Column not found

Thanks!

There's no problem with your WHERE clause, you just haven't used the right format for IsFullyInvoiced:

select ShipDate, CustomerRefFullName,ShipAddressCity,PONumber,SalesOrderLineItemRefFullName,
SalesOrderLineQuantity,SalesOrderLineDesc,ShipMethodRefFullName,TemplateRefFullName,
IsFullyInvoiced, IsManuallyClosed,RefNumber from salesorderLine nosync
WHERE Txndate >= {d '2006-05-01'} AND isfullyinvoiced=TRUE

Valid values for IsFullyInvoiced are 1, 0, TRUE, FALSE as can been seen using:

sp_columns SalesOrderLine

Ok, works great. Now, I am trying to add some more WHERE clauses but again am struggling with the format for TemplateRefFullName. I ran the sp salesorderline in vbdemo but can't make heads or tails of what it's talking about for TemplateRefFullName. Not sure what %s means.

select ShipDate, CustomerRefFullName,ShipAddressCity,PONumber,SalesOrderLineItemRefFullName,SalesOrderLineQuantity,
SalesOrderLineDesc,ShipMethodRefFullName,TemplateRefFullName,IsFullyInvoiced,IsManuallyClosed,RefNumber
from salesorderLine nosync WHERE Txndate >= {d '2006-05-01'}
AND isfullyinvoiced=false AND ismanuallyclosed=false AND TemplateRefFullName=PP Work Sales Order

Thanks!

%s means string of undefined length.

String literals in your queries need to be in single quotes as in:

select ShipDate, CustomerRefFullName,ShipAddressCity,PONumber,SalesOrderLineItemRefFullName,SalesOrderLineQuantity,
SalesOrderLineDesc,ShipMethodRefFullName,TemplateRefFullName,IsFullyInvoiced,IsManuallyClosed,RefNumber
from salesorderLine nosync WHERE Txndate >= {d '2006-05-01'}
AND isfullyinvoiced=false AND ismanuallyclosed=false AND TemplateRefFullName='PP Work Sales Order'

Ok, another follow-up. I want to limit the templates to 3 or 4 in my pass-through query. I've tried it several different ways. With the SQL I tried below, I think it would work, but it wants to re-optimize.

Select Customerreffullname, ShipAddressCity, shipdate, invoicelineservicedate, InvoiceLineItemRefFullName, TemplateRefFullName from invoiceLine unoptimized WHERE Txndate >= {d '2006-02-01'} AND InvoiceLineItemRefFullName='WW:Bin' AND TemplateRefFullName='Pickup Request' OR TemplateRefFullName='Bin Invoice'

Thank you!

You have used the UNOPTIMIZED tag, so it will reload the query directly from QuickBooks, try:

Select Customerreffullname, ShipAddressCity, shipdate, invoicelineservicedate, InvoiceLineItemRefFullName, TemplateRefFullName from invoiceLine NOSYNC WHERE Txndate >= {d '2006-02-01'} AND InvoiceLineItemRefFullName='WW:Bin' AND (TemplateRefFullName='Pickup Request' OR TemplateRefFullName='Bin Invoice')

You should also have a bracket around the two TemplateRefFullNames.