Hi all,
My older version code uses executesqlQuery to execute a SQL code and it returns dictionary value . They used the rule in main interface to save the values to local variables. Now the function is deprecated and we replace it with executeStoredProcedureForQuery to execute SQL and it returns Map structure. But we are getting error while saving that in the main interface.
This is the error we are getting. I have changed the index value as the new structure but still I am getting error. Anyone have idea about how to resolve that?
Thanks,
Kavya
Discussion posts and replies are publicly visible
Can you share the affected code snippet?
Hi Stefan,
In the main interface there will be a search button and when we click on that it will save the value.
a!buttonWidget( label: "Search", style: "NORMAL", value: true, saveInto: { local!search, if( local!refreshSearch, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_RESTRICT, { a!save(ri!gridAvailData, null), a!save(ri!summaryDs, null) }, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_DETAILS, { a!save(ri!gridRestrictData, null), a!save(ri!summaryDs, null) }, if( local!tabNbr = cons!GOS_ORDER_SEARCH_TAB_SUMMARY, { a!save(ri!gridAvailData, null), a!save(ri!gridRestrictData, null), }, {} ) ) ), {} ), { a!save(ri!generatedDocument, null), a!save( local!countRecognizedIds, index(index(index( rule!GOS_getRefBatchOrCpdMetadataByFieldAndValueSQL( fieldName: index( ri!browseInventoryHelper, "searchIdField", {} ), fieldValues: local!searchIdValues, getCountOnly: true ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ), a!save( local!restrictData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: index( ri!browseInventoryHelper, "searchIdValue", {} ), getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!notificationData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: index( ri!browseInventoryHelper, "searchIdValue", {} ), getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!restrictGridSelection.startIndex, 1 ), a!save( local!notificationGridSelection.startIndex, 1 ), a!save( ri!gridRestrictData, rule!GOS_setRestrictionsFromSQL(local!restrictData) ), a!save( ri!gridNotificationData, rule!GOS_setRestrictionsFromSQL(local!notificationData) ), a!save(local!viewAllIds, false), a!save(local!searchUnavail, false), a!save(local!isSummaryView, false), if( local!isRestrictionSearch, { a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_RESTRICT ), a!save( local!restrictData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!restrictGridSelection.startIndex, 1 ), a!save( ri!gridRestrictData, rule!GOS_setRestrictionsFromSQL(local!restrictData) ), a!save( local!batchRestrictData, index( rule!GOS_getBatchRestrictionsBySearchValues( searchFieldId: "batIdentifier", searchValues: index(ri!gridRestrictData, "batIdentifier", {}), pagingInfo: a!pagingInfo(startIndex:1, batchSize:- 1, sort:{}), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), getCountOnly: false, getBatIdentifierOnly: false ), "data", {} ) ), if( rule!APN_isEmpty(ri!gridRestrictData), {}, { a!save( ri!gridRestrictData.startDate, index(local!batchRestrictData, "startDate", {}) ), a!save( ri!gridRestrictData.endDate, index(local!batchRestrictData, "endDate", {}) ) } ), a!save( local!countRestricted, if( rule!APN_isEmpty(ri!gridRestrictData), 0, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) ) }, if( local!isNotificationSearch, { a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_NOTIFICATION ), a!save( local!notificationData, index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: false, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}) ), a!save( local!notificationGridSelection.startIndex, 1 ), a!save( ri!gridNotificationData, rule!GOS_setRestrictionsFromSQL(local!notificationData) ), a!save( local!batchRestrictData, index( rule!GOS_getBatchRestrictionsBySearchValues( searchFieldId: "batIdentifier", searchValues: index( ri!gridNotificationData, "batIdentifier", {} ), pagingInfo: a!pagingInfo(startIndex:1, batchSize:- 1, sort:{}), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), getCountOnly: false, getBatIdentifierOnly: false ), "data", {} ) ), if( rule!APN_isEmpty(ri!gridNotificationData), {}, { a!save( ri!gridNotificationData.startDate, index(local!batchRestrictData, "startDate", {}) ), a!save( ri!gridNotificationData.endDate, index(local!batchRestrictData, "endDate", {}) ) } ), a!save( local!countNotification, if( rule!APN_isEmpty( rule!GOS_setRestrictionsFromSQL(local!notificationData) ), 0, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) ) }, { a!save( local!tabNbr, cons!GOS_ORDER_SEARCH_TAB_DETAILS ), a!save( local!currentDataTabs, cons!GOS_ORDER_SEARCH_TAB_DETAILS ), a!save( local!gridAvailSelection, local!gridAvailPagingDefault ), local!sqlResult: index(index( rule!GOS_getRefBatchesToSearchBeforeCreationSQL( isExport: true, idField: index( ri!browseInventoryHelper, "searchIdField", {} ), idValue: local!searchIdValues, siteId: index( ri!browseInventoryHelper, "searchSiteIds", {} ), solventId: index( ri!browseInventoryHelper, "searchSolventIds", {} ), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ), minAmount: local!minSearchAmtStd, minConcentration: if( and( not( rule!APN_isBlank( index( ri!browseInventoryHelper, "minConcentration", {} ) ) ), index( ri!browseInventoryHelper, "minConcentration", {} ) <> local!minSearchConcStd ), index( ri!browseInventoryHelper, "minConcentration", {} ), local!minSearchConcStd ), available: true, isSolidOrderType: local!isSolidOrderType, maxAmount: if( or( rule!APN_isBlank(local!maxSearchAmtStd), local!maxSearchAmtStd = 0 ), null, local!maxSearchAmtStd ), maxConcentration: if( or( rule!APN_isBlank(local!maxSearchConcStd), local!maxSearchConcStd = 0 ), null, local!maxSearchConcStd ), oldestSynthDate: index( ri!browseInventoryHelper, "oldestSynthesisDate", {} ), newestSynthDate: index( ri!browseInventoryHelper, "newestSynthesisDate", {} ), chemistName: index( ri!browseInventoryHelper, "chemistName", {} ), minMw: index(ri!browseInventoryHelper, "minMw", {}), maxMw: if( or( rule!APN_isBlank( index(ri!browseInventoryHelper, "maxMw", {}) ), index(ri!browseInventoryHelper, "maxMw", {}) = 0 ), null, index(ri!browseInventoryHelper, "maxMw", {}) ), origin: index(ri!browseInventoryHelper, "origin", {}), concUnit: if( or( index( ri!browseInventoryHelper, "concUnits", {} ) = cons!GOS_REF_UNIT_CONCENTRATION_MGML, index( ri!browseInventoryHelper, "concUnits", {} ) = cons!GOS_REF_UNIT_CONCENTRATION_MM ), index( rule!GOS_getConcUnitIdByConcUnit(ri!browseInventoryHelper.concUnits), "description", {} ), {} ),
), "parameters",{} ),"SQLOUTPUT_CURSOR",{}), local!sqlData: rule!GOS_setRefInvFromSQL(local!sqlResult), a!save(ri!gridAvailData, local!sqlData), a!save( local!countRestricted, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: false(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ), a!save( local!countNotification, index(index(index( rule!GOS_getBatchRestrictionsBySearchValuesSQL( searchFieldId: index( ri!browseInventoryHelper, "searchIdField", {} ), searchValues: local!searchIdValues, getCountOnly: true, getBatIdentifierOnly: false, requester: loggedInUser(), notificationTab: true(), stateId: index( ri!browseInventoryHelper, "searchStateId", {} ) ), "parameters",{} ),"SQLOUTPUT_CURSOR",{}),"COUNT",{}) ) } ) ) }, a!save(local!refreshSearch, false) }, disabled: or( rule!APN_isBlank(local!searchIdValues), and( not( rule!APN_isBlank(local!prelimSearchIdValues) ), rule!APN_isBlank(local!searchIdValues) ), and(local!anyStateFilterCorrect), if( not( rule!APN_isBlank(local!searchIdValuesRaw) ), false, or( if( rule!APN_isBlank(local!searchIdValues), true, false ), and( local!tabNbr = 2, or(rule!APN_isEmpty(local!searchIdValues)) ) ) ) ), submit: false, validate: true, validationGroup: "searchButton" )
A sample sub rule for SQL statement.
if( or( rule!APN_isBlank( ri!searchFieldId ), rule!APN_isBlank( ri!searchValues ) ), {}, a!localVariables( local!totalSearchCount: count( ri!searchValues ), local!numberOfIns: tointeger( rounddown( local!totalSearchCount / cons!GOS_SQL_ID_SEARCH_SPLIT, 0 ) ) + 1, local!indexes: enumerate( local!numberOfIns ) + 1, /*GoSRT V3.2 Fix*/ local!userSiteId: if( rule!APN_isBlank( ri!order.recipientUserId ), "", index( rule!GOS_getUserSiteById( ri!order.recipientUserId ), "siteId", 0 ) ), /*GoSRT V3 - Passed Order's project code instead of Users Project Code */ local!requesterProjectIds: if( not( rule!APN_isBlank( ri!requester ) ), touniformstring( index( index( rule!GOS_getProjectIdsByUsername( username: ri!requester, pagingInfo: a!pagingInfo( 1, - 1, {} ) ), "data", {} ), "projectId", {} ) ), {} ), local!usersProjectIds: if( and(not( rule!APN_isBlank( ri!orderProjectId ) ),not( rule!APN_isEmpty( local!requesterProjectIds ) )), if( contains( local!requesterProjectIds, ri!orderProjectId ), ri!orderProjectId, {} ), {} ), local!queryStatement: if( ri!getBatIdentifierOnly, "SELECT BAT_IDENTIFIER ", if( ri!getCountOnly, "SELECT count(BAT_IDENTIFIER) as count ", "SELECT BAT_IDENTIFIER, CPD_IDENTIFIER, PROJECT_ID, RESTRICT_REASON_TIER, RESTRICT_REASON_DESC, RESTRICT_TYPE " ) ) & "FROM GOS_APN_RESTRICT_ACTV_REF_V WHERE " & "(" & joinarray( a!forEach( items:local!indexes, expression:rule!GOS_createIdentifierSearchForSQL( searchIdValues: ri!searchValues, searchIdField: ri!SearchFieldId, index: fv!item, multiplier: cons!GOS_SQL_ID_SEARCH_SPLIT, useSearch: false ) ) ) & ")" & /*"AND (START_DATE<= CURRENT_DATE AND END_DATE>= CURRENT_DATE)"&*/ if( ri!notificationTab = true, {}, if( rule!APN_isBlank( local!usersProjectIds ), {}, { " AND PROJECT_ID <>(" & rule!GOS_transformIdValuesForSQL( local!usersProjectIds ) & ")" } ) ) & if( or( rule!APN_isBlank( ri!stateId ), ri!stateId = cons!GOS_ANY_VALUE ), {}, { " AND RESTRICT_TYPE_ID in (" & if( ri!stateId = cons!GOS_REFERENCE_ID_SOLID, rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_SOLIDS } ), if( ri!stateId = cons!GOS_REFERENCE_ID_LIQUID, rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_LIQUIDS } ), rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_RESTRICT_TYPE_ID_TOTAL, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_SOLIDS, cons!GOS_REF_RESTRICT_TYPE_ID_PARTIAL_LIQUIDS } ) ) ) & ")" } ) & if( ri!notificationTab = true, { " AND RESTRICT_REASON_TIER in (" & rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_5 } ) & ")" }, { " AND RESTRICT_REASON_TIER not in (" & rule!GOS_transformIdValuesForSQL( { cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_4, cons!GOS_REF_SUB_TYPE_RESTRICT_TIER_5 } ) & ")" } ) & if( ri!isSortBatIdentifier, { " ORDER BY BAT_IDENTIFIER" & if( true, " ASC", " DESC" ) }, {} ), local!queryOutput: a!executeStoredProcedureForQuery( dataSource: cons!GOS_SQL_JNDI_NAME, procedureName:cons!GOS_SP_EXECUTE_SQL_QUERY, inputs:{a!storedProcedureInput(name:"SQLQUERY" ,value:local!queryStatement)} ), local!batchIdentifiers: index( index( local!queryOutput, "result", null ), "BAT_IDENTIFIER", null ), local!siteIds: a!forEach( items: local!batchIdentifiers, expression: rule!GOS_getBatchSiteByBatchIdForNotification( fv!item ) ), local!notificationOutput: remove( index( local!queryOutput, "result", null ), wherecontains( tointeger( local!userSiteId ), tointeger( local!siteIds ) ) ),if( ri!siteCheck = true, local!notificationOutput, local!queryOutput ) /*local!queryStatement*/ ) )
Sometimes ri!name << fn!sum error occurs when the evaluation of an if condition returns array of true or false instead of single value in the saveInto parameter.