Hello,
We are trying to convert queries in our SuiteApps to work with NetSuite2.com but running into issues with some joins. For example, the following works
select itm.id, itm.itemid as item, itm.description as itmdesc, itm.weight , itm.custitem_abc_code, itm.upccode, itm.custitem_pe_eancode
, itm.countryofmanufacture as cntry_origin, itm.custitem_rohs_compliant
, itm.mpn, itm.custitem_msrp, itm.custitem_pe_mappricing, itm.itemtype, itm.custitem_buyer
, uom.abbreviation as purch_uom
, iven.vendorcode
from Item itm
left join unitsTypeUOM uom on uom.internalid = itm.purchaseunit
left join itemVendor iven on iven.item = itm.id and preferredvendor = 'Yes'
where isinactive = 'F'
But as soon as I try to join to the employee table so we can get the buyer name
select itm.id, itm.itemid as item, itm.description as itmdesc, itm.weight , itm.custitem_abc_code, itm.upccode, itm.custitem_pe_eancode
, itm.countryofmanufacture as cntry_origin, itm.custitem_rohs_compliant
, itm.mpn, itm.custitem_msrp, itm.custitem_pe_mappricing, itm.itemtype, itm.custitem_buyer
, uom.abbreviation as purch_uom
, iven.vendorcode
, itm.custitem_buyer
from Item itm
left join unitsTypeUOM uom on uom.internalid = itm.purchaseunit
left join itemVendor iven on iven.item = itm.id and preferredvendor = 'Yes'
left join employee emp on emp.id = itm.custitem_buyer
where isinactive = 'F'
we get the message
<c1>: S1000 400 [NetSuite][ODBC 32bit driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# m8he5q6vk5twx8x4ncxr[400]
20-Mar-2025 09:32:56
This happens with other tables as well. From what I understand in the Records Catalog this is a valid join. We even tried an inner join and still the same issue.
Why can't we join to some tables?
Thanks!