This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Does anyone know if it is possible to declare parameters in a SuiteQL Query? I tried couple of ways but did not succeed so far.

`var rows = query.runSuiteQL({ query: 'select * from transaction where id = ? ', parameters: [ '123456' ] }).asMappedResults();`

thanks for the info but what if I want to use same parameter couple of times in a single query. I want to use a single parameter embedded in the SQL

it will become a procedure and then it is not possible to run as per my testing

`DECLARE`
  `tranid integer := 30777853;`
`BEGIN`
  `SELECT * from TRANSACTION T`
  `WHERE T.id = tranid;`
`END`

Let me mention this to <@U856E2SEA> he should know best!

That won't work. You're trying to do PL/SQL

If you want to use the same parameter multiple times, you have to pass it more than once in `parameters`.

It's a shame it doesn't support the `:param1` bind syntax that's used elsewhere in the programming world

You have to remember that there's an abstraction layer between you and the actual database, and the SQL you're writing is not the "real" SQL that gets executed

<@UNJNU4RT8> You can use a WITH clause (a.k.a. "subquery factoring") and a query against the DUAL table to simulate DECLARE. For example:
```WITH Temp AS 
	( SELECT 22993 AS TranID FROM DUAL )
SELECT
	Transaction.*
FROM 
	Transaction
	INNER JOIN Temp ON
		( Temp.TranID = Temp.TranID )
WHERE
	( ID = Temp.TranID )```
It's awkward, but it works, and essentially achieves the same goal.

thanks <@U856E2SEA>, that works for SuiteQL but fails on ODBC :slightly_smiling_face: I think I'll not force my chances for a single query that can be run on both endpoints

I do hate SQL.  Requires too much 'clever' code (which is a flaw, not an achievement) and has no testing tools, intellisense, etc. in the NS space. I'll try and keep SQL queries simple myself.

tell me about it when you need to query Trial Balance Report through SQL :grin: