https://netsuiteprofessionals.com logo
Join Slack
Powered by
<@U856E2SEA> I have issue when use subselect in qu...
# suiteql
z
@tdietrich I have issue when use subselect in query and SuiteQLPaged execution
Copy code
var sql_from = "select table1.field1 as t1data, " +
		" (select table2.field2 from table2 where table2.id2 = table1.id1) as t2data "
	+ " where (table1.id = ?)";

var myPagedResults = query.runSuiteQLPaged({
pageSize : 1000,
query : sql_from,
params : [ masterId ]
});

var retArr = [];
var iterator = myPagedResults.iterator();

iterator.each(function(resultPage) {
var currentPage = resultPage.value;
var theData = currentPage.data.asMappedResults();
for (var a = 0; a < theData.length; a++) {
retArr.push(theData[a]);
}
return true;
});
Table1 has more than 10k records in results, and NetSuite raised error during iteration. Strange, it works when I prepare SQL statement inside your SQL Tools (I am always doing that 🙂 ), of course without ? (I replace that with value), but I receive only 4000 records. Now, I have workaround, because it is part of M/R getting data stage, inside map stage an additional sql-query (subselect table2) is executed. Could you please test this issue and if you confirm, it would be reason for NetSuite case ticket
t
@Zoran Roncevic What error is being thrown?
z
Copy code
{
  "type": "error.SuiteScriptError",
  "name": "SSS_SEARCH_ERROR_OCCURRED",
  "message": "Search error occurred: Invariant check for stage 'PaginationOperation' check failed for 'SELECT [5x] FROM [1x] GROUP BY [1x]'",
  "stack": [
    "runSuiteQLPagedInternal(N/query)",
    "getInputData(/SuiteScripts/RSM_EBANK/rsm_bstmt_payments_mr.js:36)"
  ],
  "cause": {
    "type": "internal error",
    "code": "SSS_SEARCH_ERROR_OCCURRED",
    "details": "Search error occurred: Invariant check for stage 'PaginationOperation' check failed for 'SELECT [5x] FROM [1x] GROUP BY [1x]'",
    "userEvent": null,
    "stackTrace": [
      "runSuiteQLPagedInternal(N/query)",
      "getInputData(/SuiteScripts/RSM_EBANK/rsm_bstmt_payments_mr.js:36)"
    ],
    "notifyOff": false
  },
  "id": "",
  "notifyOff": false,
  "userFacing": false
}
As I wrote. exactly same SQL query works fine with your SQL query tool. But you are using:
Copy code
var queryResults = query.runSuiteQL(
						{
							query: context.request.parameters.custpage_field_query
						}
					);
t
It looks like a bug, and my guess is that it's struggling with that subquery. You might want to try something like this...
Copy code
var sql_from = [
		'SELECT ',
		'table1.field1 AS t1data, ',
		'table2.field2 ',
		'FROM ',
		'table1 ',
		'INNER JOIN table2 ON table2.id2 = table1.id1) ',
		'WHERE ',
		'(table1.id = ?)'	
    ].join("\n");
z
ahh, i sent here simplified version 🙂.
Copy code
var sql_from = "select bdp.custrecord_rsm_bdp_customer as v_customer " + ", bdp.custrecord_rsm_bdp_amount_p as v_amount " + ", NVL2(bdp.custrecord_rsm_bdp_memo_p,bdp.custrecord_rsm_bdp_memo_p,'') as v_memo "
				+ ", bstmt.custrecord_rsm_bstmt_subsidiary as v_subsidiary " + ", bstmt.custrecord_rsm_bstmt_bank_acct as v_coabank " + ", bstmt.custrecord_rsm_bstmt_currency as v_currency "
				+ ", bstmt.custrecord_rsm_bstmt_trandate as v_trandate " + ", bdp.id as v_bdpid " + ", BUILTIN.DF(custrecord_rsm_bdp_action) as v_action " + ", jcust.receivablesaccount as v_aracct "
				+ ", (NVL2(bdp.custrecord_rsm_bdp_transaction_recog, bdp.custrecord_rsm_bdp_transaction_recog, -1)) as v_tran_recog "
				+ " from customrecord_rsm_bank_data_parsed bdp join customrecord_snt_bank_statement bstmt on bdp.custrecord_rsm_bdp_parent = bstmt.id " + " join customer jcust on jcust.id = bdp.custrecord_rsm_bdp_customer "
				+ " where (NVL2(custrecord_rsm_bdp_transaction,'T','F') = 'F' ) AND (bstmt.id = ?)";
I don't expect from anyone to understand this 🙂 . Problem I have is how to determine type of linked transaction from my customfield (custrecord_rsm_bdp_transaction_recog field). It is only integer ID = transaction.id, and I need to what type (SalesOrder, Invoice...) is. For that I have to query transaction table (select type from transaction where id = recog). I wanted just to share my query examples with others. It is part of bigger project to create NetSuite transactions from Bank Statement :)
5 Views
Open in Slack
PreviousNext
Powered by