How can I convert SuiteQL query code into corresponding json format for REST API call

Question

I have the following SuiteQL code generated using the NetSuite: Workbook Export chrome extension:

`

SELECT 
  BUILTIN_RESULT.TYPE_DATE(TRANSACTION.trandate) AS trandate, 
  BUILTIN_RESULT.TYPE_STRING(TRANSACTION.tranid) AS tranid, 
  BUILTIN_RESULT.TYPE_STRING(item.displayname) AS displayname, 
  BUILTIN_RESULT.TYPE_CURRENCY(BUILTIN.CONSOLIDATE(transactionLine.rate, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 100, 'DEFAULT'), BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(transactionLine.rate, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 100, 'DEFAULT'))) AS rate, 
  BUILTIN_RESULT.TYPE_FLOAT(transactionLine.quantity) AS quantity
FROM 
  TRANSACTION, 
  item, 
  transactionLine, 
  (SELECT 
    PreviousTransactionLink.nextdoc AS nextdoc, 
    PreviousTransactionLink.nextdoc AS nextdoc_join, 
    transaction_SUB.name_crit AS name_crit_0
  FROM 
    PreviousTransactionLink, 
    (SELECT 
      transaction_0.ID AS ID, 
      transaction_0.ID AS id_join, 
      CUSTOMLIST234.name AS name_crit
    FROM 
      TRANSACTION transaction_0, 
      CUSTOMLIST234
    WHERE 
      transaction_0.custbody1 = CUSTOMLIST234.ID(+)
    ) transaction_SUB
  WHERE 
    PreviousTransactionLink.previousdoc = transaction_SUB.ID(+)
  ) PreviousTransactionLink_SUB
WHERE 
  (((transactionLine.item = item.ID(+) AND TRANSACTION.ID = transactionLine.TRANSACTION) AND TRANSACTION.ID = PreviousTransactionLink_SUB.nextdoc(+)))
   AND ((TRANSACTION.TYPE IN ('CustInvc') AND transactionLine.itemtype IN ('InvtPart', 'Kit') AND NVL(transactionLine.mainline, 'F') = ? AND (UPPER(PreviousTransactionLink_SUB.name_crit_0) NOT LIKE ? OR PreviousTransactionLink_SUB.name_crit_0 IS NULL) AND TRUNC(TRANSACTION.trandate) > TO_DATE(?, 'YYYY-MM-DD')))

`

When I try to paste it into Power Automate's API call, I get the following 400 error:

"Invalid search query. Detailed unprocessed description follows. Invalid number of parameters. Expected: 3. Provided: 0."

My call's query was formatted as follows:

`

{
  "q": "SELECT BUILTIN_RESULT.TYPE_DATE(TRANSACTION.trandate) AS trandate, BUILTIN_RESULT.TYPE_STRING(TRANSACTION.tranid) AS tranid, BUILTIN_RESULT.TYPE_STRING(item.displayname) AS displayname, BUILTIN_RESULT.TYPE_CURRENCY(BUILTIN.CONSOLIDATE(transactionLine.rate, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 100, 'DEFAULT'), BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(transactionLine.rate, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 100, 'DEFAULT'))) AS rate,   BUILTIN_RESULT.TYPE_FLOAT(transactionLine.quantity) AS quantity FROM TRANSACTION, item, transactionLine, (SELECT  PreviousTransactionLink.nextdoc AS nextdoc,     PreviousTransactionLink.nextdoc AS nextdoc_join,     transaction_SUB.name_crit AS name_crit_0 FROM PreviousTransactionLink,   (SELECT transaction_0.ID AS ID,transaction_0.ID AS id_join,    CUSTOMLIST234.name AS name_crit FROM TRANSACTION transaction_0,     CUSTOMLIST234 WHERE transaction_0.custbody1 = CUSTOMLIST234.ID(+) ) transaction_SUB WHERE PreviousTransactionLink.previousdoc = transaction_SUB.ID(+) ) PreviousTransactionLink_SUB WHERE   (((transactionLine.item = item.ID(+) AND TRANSACTION.ID = transactionLine.TRANSACTION) AND TRANSACTION.ID = PreviousTransactionLink_SUB.nextdoc(+))) AND ((TRANSACTION.TYPE IN ('CustInvc') AND transactionLine.itemtype IN ('InvtPart', 'Kit') AND NVL(transactionLine.mainline, 'F') = ? AND (UPPER(PreviousTransactionLink_SUB.name_crit_0) NOT LIKE ? OR PreviousTransactionLink_SUB.name_crit_0 IS NULL) AND TRUNC(TRANSACTION.trandate) > TO_DATE(?, 'YYYY-MM-DD')))"
}

`

When I try using SuiteQL in my API calls using a simple query, my API calls work, so I'm pretty sure I'm screwing up the fomrat of the above's json format. I tried the following simple query call and it was succesfull:

`

{
  "q": "SELECT email, COUNT(*) as count FROM transaction GROUP BY email"
}

`

I have tried using json beautifier to try to fix my json but I haven't been able to do so successfully.

Below is a pic of the HTTP action I'm using in power Automate to make the query:

HTTP POST Action

For context, I'm an accountant by trade trying to learn how to do some basic coding. Any hint that will help me correctly format the above query will be greatly appreciated. Thanks!

Answer 1

As per my comment, typically, question marks in an SQL statement are deemed as being parameters.

Code based frameworks use them as placeholders for filling the parameters to abstract them from the string itself.

Now, in PowerAutomate and with your question, I think it's just a little bit of seeing the forest for the trees.

The easiest way to populate the question marks is to literally replace them in the string itself with the relevant variable or expression.

So if I took an SQL statement with a parameter like you have ...

SELECT * FROM [dbo].[Test] WHERE Parameter = ?

... I can do the following (this is an EXTREMELY basic example) ...

Using the expressions, you can populate strings within other variables or steps in the flow.

Just be careful when it comes to encapsulating your different parameters with quotes, either single or double. The SQL statement will need them in the cases where values are numbers.