Hello! I'm running into an issue with a ODBC query I'm trying to write for the Netsuite v2 data source that involves the orderLine table.
When I run the query in SuiteQL it works just fine and returns the expected results. However when I run the query through ODBC I get the error: "[NetSuite][ODBC 64bit driver][OpenAccess SDK SQL Engine]Syntax Error in the SQL statement.[10104]".
If I remove the orderLine selects and joins from my ODBC query the rest of it works fine.
I remember reading that ODBC queries have some nuances to them and some syntax and commands are different or just don't work. I'm starting to wonder if this is one of those instances?
Here's the query I'm trying to run for a shipping label template:
/* START */
SELECT
tranid as SalesOrder,
Customer.entityNumber as CusNum,
custbody_bf_end_cust_po as CustomerPO,
orderLine.line as CustomerPOLine,
orderLine.quantity as Quantity,
CUSTOMRECORD_SCM_CUSTOMERPARTNUMBER.name as CustomerPartNum,
transaction.custbody_ifshipdate as ShipDate,
transactionShippingAddress.addressee,
transactionShippingAddress.addr1,
transactionShippingAddress.addr2,
transactionShippingAddress.city,
transactionShippingAddress.state,
transactionShippingAddress.zip
FROM transaction
join transactionShippingAddress on transactionShippingAddress.nkey = transaction.shippingaddress
join Customer on Customer.id = transaction.entity
join CUSTOMRECORD_SCM_CUSTOMERPARTNUMBER on CUSTOMRECORD_SCM_CUSTOMERPARTNUMBER.custrecord_scm_cpn_customer = Customer.id
join orderLine on orderLine.order= transaction.id
WHERE transaction.type = 'SalesOrd'
/* END */
Please let me know your thoughts or if you have any ideas that I should try.
Thanks!
