SuiteSQL Query
I am running this query through SuiteQL to integrate with NetSuite; select * from item fetch first 5 rows only.
The above returns the following;
{ “items”: [ { “autoleadtime”: “T”, “autopreferredstocklevel”: “T”, “autoreorderpoint”: “T”, “averagecost”: “0”, “copydescription”: “F”, “createddate”: “5/30/2018”, “enforceminqtyinternally”: “F”, “froogleproductfeed”: “F”, “fullname”: “******”, “generateaccruals”: “F”, “id”: “1017”, “includechildren”: “F”, “isdropshipitem”: “F”, “isfulfillable”: “F”, “isinactive”: “F”, “isonline”: “F”, “isphantom”: “F”, “isspecialorderitem”: “F”, “itemid”: “******”, “itemtype”: “Description”, “lastmodifieddate”: “12/12/2019”, “lastpurchaseprice”: “0”, “matchbilltoreceipt”: “F”, “nextagproductfeed”: “F”, “printitems”: “F”, “seasonaldemand”: “F”, “shipindividually”: “F”, “shoppingproductfeed”: “F”, “shopzillaproductfeed”: “F”, “subsidiary”: “1, 4”, “yahooproductfeed”: “F” }, { “autoleadtime”: “T”, “autopreferredstocklevel”: “T”, “autoreorderpoint”: “T”, “averagecost”: “0”, “copydescription”: “F”, “createddate”: “2/6/2025”, “custitem_filter_job_customer”: “41310”, “custitem_nm_woo_item_yesno”: “F”, “custitem_ns_sc_ext_ts_exclude”: “F”, “custitem_ns_sc_ext_ts_override”: “F”, “custitem_solupaysp_engstatus”: “1”, “displayname”: “1002 S Ahsahka Ave (Rentwise)”, “enforceminqtyinternally”: “F”, “froogleproductfeed”: “F”, “fullname”: “1002 S Ahsahka Ave (Rentwise)”, “generateaccruals”: “F”, “id”: “15067”, “includechildren”: “F”, “isdropshipitem”: “F”, “isfulfillable”: “F”, “isinactive”: “F”, “isonline”: “F”, “isphantom”: “F”, “isspecialorderitem”: “F”, “itemid”: “1002 S Ahsahka Ave (Rentwise)”, “itemtype”: “Group”, “lastmodifieddate”: “2/6/2025”, “lastpurchaseprice”: “0”, “matchbilltoreceipt”: “F”, “nextagproductfeed”: “F”, “printitems”: “T”, “seasonaldemand”: “F”, “shipindividually”: “F”, “shoppingproductfeed”: “F”, “shopzillaproductfeed”: “F”, “subsidiary”: “1”, “yahooproductfeed”: “F” }, { “autoleadtime”: “T”, “autopreferredstocklevel”: “T”, “autoreorderpoint”: “T”, “averagecost”: “0”, “copydescription”: “F”, “createddate”: “10/24/2022”, “custitem_filter_job_customer”: “18616”, “custitem_nm_woo_item_yesno”: “F”, “custitem_ns_sc_ext_ts_exclude”: “F”, “custitem_ns_sc_ext_ts_override”: “F”, “custitem_solupaysp_engstatus”: “1”, “displayname”: “10041 West Garverdale Lane 101 (208 Prop)”, “enforceminqtyinternally”: “F”, “froogleproductfeed”: “F”, “fullname”: “10041 West Garverdale Lane 101 (208 Prop)”, “generateaccruals”: “F”, “id”: “11352”, “includechildren”: “F”, “isdropshipitem”: “F”, “isfulfillable”: “F”, “isinactive”: “F”, “isonline”: “F”, “isphantom”: “F”, “isspecialorderitem”: “F”, “itemid”: “10041 West Garverdale Lane 101 (208 Prop)”, “itemtype”: “Group”, “lastmodifieddate”: “1/29/2024”, “lastpurchaseprice”: “0”, “matchbilltoreceipt”: “F”, “nextagproductfeed”: “F”, “printitems”: “T”, “seasonaldemand”: “F”, “shipindividually”: “F”, “shoppingproductfeed”: “F”, “shopzillaproductfeed”: “F”, “subsidiary”: “1”, “yahooproductfeed”: “F” }, { “autoleadtime”: “T”, “autopreferredstocklevel”: “T”, “autoreorderpoint”: “T”, “averagecost”: “0”, “copydescription”: “F”, “createddate”: “10/24/2022”, “custitem_ns_sc_ext_ts_exclude”: “F”, “custitem_ns_sc_ext_ts_override”: “F”, “custitem_solupaysp_engstatus”: “1”, “displayname”: “10041 West Garverdale Lane 102 (208 Prop)”, “enforceminqtyinternally”: “F”, “froogleproductfeed”: “F”, “fullname”: “10041 West Garverdale Lane 102 (208 Prop)”, “generateaccruals”: “F”, “id”: “11353”, “includechildren”: “F”, “isdropshipitem”: “F”, “isfulfillable”: “F”, “isinactive”: “F”, “isonline”: “F”, “isphantom”: “F”, “isspecialorderitem”: “F”, “itemid”: “10041 West Garverdale Lane 102 (208 Prop)”, “itemtype”: “Group”, “lastmodifieddate”: “10/24/2022”, “lastpurchaseprice”: “0”, “matchbilltoreceipt”: “F”, “nextagproductfeed”: “F”, “printitems”: “T”, “seasonaldemand”: “F”, “shipindividually”: “F”, “shoppingproductfeed”: “F”, “shopzillaproductfeed”: “F”, “subsidiary”: “1”, “yahooproductfeed”: “F” }, { “autoleadtime”: “T”, “autopreferredstocklevel”: “T”, “autoreorderpoint”: “T”, “averagecost”: “0”, “copydescription”: “F”, “createddate”: “10/24/2022”, “custitem_ns_sc_ext_ts_exclude”: “F”, “custitem_ns_sc_ext_ts_override”: “F”, “custitem_solupaysp_engstatus”: “1”, “displayname”: “10041 West Garverdale Lane 201 (208 Prop)”, “enforceminqtyinternally”: “F”, “froogleproductfeed”: “F”, “fullname”: “10041 West Garverdale Lane 201 (208 Prop)”, “generateaccruals”: “F”, “id”: “11354”, “includechildren”: “F”, “isdropshipitem”: “F”, “isfulfillable”: “F”, “isinactive”: “F”, “isonline”: “F”, “isphantom”: “F”, “isspecialorderitem”: “F”, “itemid”: “10041 West Garverdale Lane 201 (208 Prop)”, “itemtype”: “Group”, “lastmodifieddate”: “10/24/2022”, “lastpurchaseprice”: “0”, “matchbilltoreceipt”: “F”, “nextagproductfeed”: “F”, “printitems”: “T”, “seasonaldemand”: “F”, “shipindividually”: “F”, “shoppingproductfeed”: “F”, “shopzillaproductfeed”: “F”, “subsidiary”: “1”, “yahooproductfeed”: “F” } ] }
The problem is that if I try to filter according to subsidiary, it doesn’t work.
When I run this query; select * from item i where i.subsidiary like ‘%4%’ fetch first 5 rows only It returns nothing.
What am I doing wrong?
Is there some weird type on the subsidiary field, that doesn’t allow for text search on it?
Hi,
Since the link is many to many, there is another table needed.
SELECT item.id, itemSubsidiaryMap.subsidiary
FROM item
JOIN itemSubsidiaryMap ON
itemSubsidiaryMap.item = item.id
WHERE itemSubsidiaryMap.subsidiary = 4
