https://netsuiteprofessionals.com logo
Join Slack
Powered by
Does anyone know if there is an undocumented limit...
# suiteql
k
Does anyone know if there is an undocumented limitation to the number of parameters you can add to a 
runSuiteQL
command? I'm testing this out right now but wondered if someone tested and knows already.
c
Its based on SQL 92 so whatever the limits of that are is what i'd expect.
k
Nice to see you in here.
c
I love me some suiteql
k
LOL
I'm asking Grok right now.
We'll see what it finds.
SQL-92 (the 1992 version of the SQL standard) does not explicitly define a hard limit on the number of parameters (e.g., values in an IN clause or placeholders like ? in a prepared statement). The standard leaves such implementation details to the discretion of the database vendors, meaning the actual limitation depends on the specific database system implementing SQL-92 rather than the standard itself.
In Oracle (up to versions like 8i/9i, which align with SQL-92 compliance), the limit for values in an IN clause is 1,000 literals
c
It must be so large you aren't gonna run into it or question your query if you do
Some of the things have different limitations
k
My query had several thousand parameter values.
It was building an IN clause with thousands of question marks in a prepared statement.
SuiteScript throws an error if it's over a certain number. It's not the size of the query or the number of values in the In clause that was causing me the problem.
So I implemented a batching process to limit the number of prepared statements for the In clause and then added results from each batch to an array and return that in the Input phase of an M/R.
c
what kind of crazy sql are you writing 😄
k
I literally just told you! LOL
It is from a temporary architecture. I've triggered a Map/Reduce from another Map/Reduce that collects record ID's and sets it as an array on a the script parameter which is then read by the input phase to build the where clause.
Typically, it's in the hundreds but there were failures in the upstream scripts that resulted in the script I'm working on to never be execute which increased the number of id's being sent in and causing the failure.
After implementing the batching process, it worked perfectly.
c
Ah gotcha. Nicely done
k
Pretty sure the limit is 1,000 though.
Any chance you know how to get at the transaction line history data? I need to find lines where a certain column value was changes on or after a certain date.
c
SystemNote table
Someone was just looking at this the other day in one of the channels as well
k
LLM was a total fail. It gave me all kinds of false information about SQL queries. It told me to query non-existent tables.
Ooo, what's SystemNote2?
Yeah, system notes is not line history.
I asked Sean. He seems to know super edge case shit like this.
c
k
c
Yeah looks like the regular one is the one to use for 99% of it
k
Yep, correct:
Copy code
select
record, 
lineid,
from systemnote 
where recordtypeid = '-30' 
and 
newvalue = 'T' 
and 
date >= '2/13/2025' 
and 
field = 'CUSTCOL_LS_CANCELLED' 
and 
context = 'RST' 
group by lineid, record
order by lineid desc
-30
is the record type for Sales Order 
RST
is abbreviation for RESTLet
This got me exactly what I was looking for.
BTW, that maximum is 2,000.
6 Views
Open in Slack
PreviousNext
Powered by