I have a query that I am executing in script. Calling

query.runSuiteQLPaged

throws the "Search error occurred: Invalid or unsupported search" error. The same query is successful using

query.runSuiteQL

but does not include all results, hence the need to run paged. I'm not sure what the difference is. Any troubleshooting tips?

Are you using

ORDER BY

?

Nope. Not in this query. Would that help?

I am not sure whether you would like this solution or not. But I am using this.

var allResults = [];
                        var paginatedRowBegin = 0;
                        var paginatedRowEnd = 5000;
                        var moreRecordsFlag  = true;
                        while(moreRecordsFlag) {
                            var paginatedSQL = 'SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM ( ' + queryString + ' ) ) WHERE ( ROWNUMBER BETWEEN ' + paginatedRowBegin + ' AND ' + paginatedRowEnd + ')';
                            var results = query.runSuiteQL({query: paginatedSQL} ).asMappedResults();
                            allResults = allResults.concat( results );
                            if ( results.length < 5000 ) {
                                moreRecordsFlag = false;
                            }
                            paginatedRowBegin = paginatedRowBegin + 5000;
                            paginatedRowEnd  = paginatedRowEnd + 5000;
                        }                        log.debug("allResults",allResults.length);

Basically handling the paging on your own. Interesting. What circumstance led you to that approach? Were you also running into problems with runSuiteQLPaged?

@Ben Tanner Not necessarily - I have just run into issues with running paged queries without being explicit about order.

Gotcha. Really I was trying to submit a SuiteQL task to have the results automatically saved to file. The task was failing even though the query runs successfully using a JDBC connection. It's a lot of trial and error to figure out what's going wrong.

This is not my solution, basically I copy pasted the timothy deitrich code, into one of our restlet. I just found this one better after a few trial and errors. Over runSuiteQLpaged.

I appreciate the context. I'll try that out and see how it goes. Right now I'm trying different order by options as well. Thank you both for the ideas.

@Ben Tanner You might get some traction by sharing the text of your query

Sure. I've written it a couple ways trying to work around errors and improve performance. Here's what I'm working with currently.

select e.LASTNAME
		, e.FIRSTNAME
		, e.entityid
		, e.TITLE
		, csm.NAME as role
		, e.EMAIL
		, d.FULLNAME as department
		, a.STATE
		, c.entitySTATUS as customer_status
		, c.ID as customer_id
		, mc.custrecord_mdl_cloud_site_code
from Customer c
	inner join CustomerSubsidiaryRelationship sub on sub.isprimarysub = 'T' and sub.subsidiary = 2 and sub.entity = c.id
	inner join CUSTOMRECORD_MDL_CLOUD mc on mc.custrecord_mdl_cloud_customer = c.id and mc.ISINACTIVE = 'F' and UPPER(mc.custrecord_mdl_cloud_site_code) not like '%CLOSED%' and UPPER(mc.custrecord_mdl_cloud_site_code) not like '%DISABLED%'
	inner join EMPLOYEE e on e.ISINACTIVE = 'F' and e.SUBSIDIARY = 2 and (c.custentity_noncorecsm3 = e.id or c.custentity_noncorecsm4 = e.id)
	inner join DEPARTMENT d on d.id = e.DEPARTMENT
	left outer join CUSTOMLIST_SUPPORTROLES csm on csm.id = e.custentity_supportrole
	left outer join customerAddressbookEntityAddress a on a.nkey = c.defaultbillingaddress
where c.entitystatus not in (16,17,69)
UNION ALL
select e.LASTNAME
		, e.FIRSTNAME
		, e.entityid
		, e.TITLE
		, sr.NAME as role
		, e.EMAIL
		, d.FULLNAME as department
		, a.STATE
		, c.entitySTATUS as customer_status
		, c.ID as customer_id
		, mc.custrecord_mdl_cloud_site_code
from Customer c
	inner join CustomerSubsidiaryRelationship sub on sub.isprimarysub = 'T' and sub.subsidiary = 2 and sub.entity = c.id
	inner join CUSTOMRECORD_MDL_CLOUD mc on mc.custrecord_mdl_cloud_customer = c.id and mc.ISINACTIVE = 'F' and UPPER(mc.custrecord_mdl_cloud_site_code) not like '%CLOSED%' and UPPER(mc.custrecord_mdl_cloud_site_code) not like '%DISABLED%'
	inner join CustomerSalesTeam st on st.customer = c.id
	inner join EMPLOYEE e on e.ISINACTIVE = 'F' and e.SUBSIDIARY = 2 and e.id = st.employee
	inner join DEPARTMENT d on d.id = e.DEPARTMENT
	inner join salesRole sr on sr.id = st.salesrole
	left outer join customerAddressbookEntityAddress a on a.nkey = c.defaultbillingaddress
where c.entitystatus not in (16,17,69)