https://netsuiteprofessionals.com logo
Join Slack
Powered by
Anyone seen issues with suiteql in getInputData or...
# suitescript
w
Anyone seen issues with suiteql in getInputData or has any tips regarding below issue? I have a query that make the getInputData fail without any errors in inputsummary.error. See code in thread 🧵
Here is the getInputData:
Copy code
const getInputData = (): { type: string, query: string } => {
    log.debug('getInput', 'start')
    const suiteQL = getInputDataQuery()
    log.debug('getInput', { suiteQL })
    const timeStart = new Date().getTime()
    const results = query.runSuiteQL({ query: suiteQL }).asMappedResults()
    log.debug('result', { resultLength: results.length, time: new Date().getTime() - timeStart, firstFiveResults: results.slice(0, 5) })
    return {
        type: 'suiteql',
        query: suiteQL,
    }
}
I've added running the query in getInputData just to verify that it is actually working and has results. The log is:
Copy code
{
   resultLength: 1133,
   time: 545,
   firstFiveResults: [
      {
         id: 3700,
         new_eur_amount: 852.54
      },
      {
         id: 4631,
         new_eur_amount: 423.05
      },
      {
         id: 4632,
         new_eur_amount: 337.9
      },
      {
         id: 4630,
         new_eur_amount: 142
      },
      {
         id: 4634,
         new_eur_amount: 230.32
      }
   ]
}
The query is a bit "special" with a CTE, but it is working and producing results without nulls
Copy code
WITH max_rate as (
  SELECT
    baseCurrency,
    transactionCurrency,
    MAX(effectiveDate) as effective_date
  FROM
    currencyRate
  WHERE
    effectiveDate <= sysdate
  GROUP BY
    baseCurrency,
    transactionCurrency
),
fx_rates as (
  SELECT
    fx.baseCurrency,
    fx.transactioncurrency,
    fx.exchangeRate,
    fx.effectiveDate
  FROM
    currencyRate fx
    JOIN max_rate ON max_rate.baseCurrency = fx.baseCurrency
    AND max_rate.transactionCurrency = fx.transactionCurrency
    AND max_rate.effective_date = fx.effectivedate
)
SELECT
  REXP.id,
  round(
    fx_rates.exchangeRate * REXP.custrecord_rexp_rech_expense_org_am,
    2
  ) as new_eur_amount
FROM
  customrecord_rexp_rech_expense REXP
  JOIN customlist_rexp_rech_exp_stages STAGE on STAGE.id = REXP.custrecord_rexp_rech_expense_stage
  JOIN fx_rates on fx_rates.baseCurrency = 1
  AND fx_rates.transactioncurrency = REXP.custrecord_rexp_rech_expense_curr
WHERE
  lower(STAGE.scriptid) IN('ready_to_be_recharged', 'partially_invoiced')
  AND round(
    nvl(REXP.custrecord_rexp_rech_expense_eur_amt, 0),
    2
  ) <> round(
    fx_rates.exchangeRate * REXP.custrecord_rexp_rech_expense_org_am,
    2
  )
e
I know this is probably not helpful, but just pointing out that there seems to be a pattern of Map/Reduce related issues over the past several days. I follow this thread pretty often and I don't remember there being so many issues related to M/R scripts.
w
thanks for the heads up!
u
does it still occur if you 
return results
instead of
Copy code
return {
        type: 'suiteql',
        query: suiteQL,
    }
w
No, if I run the query in getInputData, paginate over the full result and return everything it works without problems. I'm suspecting that the suiteql-interpreter in between getInputData and Map is different to the normal one.
But It's very convenient to not have to bother with limitations on result length and pagination.
4 Views
Open in Slack
PreviousNext
Powered by