i have a suiteql query in which i need to pass the date as the parameter. I had done this like below but it shows error var results = query.runSuiteQL({ query: 'SELECT '+ 'item.itemid, item.id, item.displayname, bomcomp.item, itemcomp.itemid,'+ 'itemcomp.displayname, bomcomp.bomquantity, itemcomp.itemtype, '+ 'itemcomp.custitem_cmb_rollup_cost, itemcomp.lastpurchaseprice '+ 'FROM assemblyitembom assbom, item, bom, bomrevision bomrev, '+ 'bomrevisioncomponent bomcomp,item itemcomp '+ 'WHERE '+ 'assbom.assembly = item.id '+ 'AND bomcomp.item = itemcomp.id '+ 'AND assbom.billofmaterials = bom.id '+ 'AND bomrev.billofmaterials = bom.id '+ 'AND bomcomp.bomrevision = bomrev.id '+ 'AND (bomrev.effectivestartdate is NULL OR bomrev.effectivestartdate <= ?)', params: [stringDate] }); could anyone please help me?

What’s the error you’re getting

SSS_SEARCH_ERROR_OCCURRED

I think the issue is with your FROM statement. I see you are referencing multiple tables without including a JOIN statement. I made an attempt at updating the query string but its difficult without knowing the data structure.

var queryString = `SELECT 
item.itemid
, item.id
, item.displayname
, bomcomp.item
, itemcomp.itemid
, itemcomp.displayname
, bomcomp.bomquantity
, itemcomp.itemtype
, itemcomp.custitem_cmb_rollup_cost
, itemcomp.lastpurchaseprice

FROM assemblyitembom as assbom
JOIN item ON assbom.assembly = item.id
JOIN bom ON assbom.billofmaterials = bom.id
JOIN bomrevision as bomrev ON bomrev.billofmaterials = bom.id
JOIN bomrevisioncomponent as bomcomp ON bomcomp.bomrevision = bomrev.id
JOIN item as itemcomp ON bomcomp.item = itemcomp.id

WHERE assbom.assembly = item.id
AND bomcomp.item = itemcomp.id
AND assbom.billofmaterials = bom.id
AND bomrev.billofmaterials = bom.id
AND bomcomp.bomrevision = bomrev.id
AND (bomrev.effectivestartdate is NULL OR bomrev.effectivestartdate <= ?)`

var results = query.runSuiteQL({
	query: queryString,
	params: [stringDate]
});

So to walk through what I did, first i pulled your query string out into a variable and wrapped it in tick marks `` to be able to format it in a way thats a little easier to read. Then i updated your FROM statement to add the joins in. Then we just call the query the same way, but we just provide the newly created queryString variable into the function.