https://netsuiteprofessionals.com logo
Join Slack
Powered by
j
message has been deleted
c
I think it's a sequential array of values to replace 
?
parameters in the query
So something like
Copy code
query.runSuiteQL({
  query: 'SELECT * FROM transaction WHERE id = ?',
  params: [ 123 ]
});
Not 100% on that, as I use string interpolation to construct queries.
e
Yeah ^ this is correct. See the Help page 
Examples of Using SuiteQL in the N/query Module
in the last section 
Use a SuiteQL Query in a Map/Reduce Script
j
hm ok
how would you use it with multiple parameters?
also: why is NetSuite documentation so horribly bad
c
Copy code
query.runSuiteQL({
  query: 'SELECT * FROM transaction WHERE statusref = ? AND entity = ?',
  params: [ A, 123 ]
});
Make sure the order of elements in the params array matches the order of appearance of the 
?
in the query.
j
ah
weird
c
Yeah, I'm not a fan. It's fragile and prone to breakage if you restructure your query -- especially for someone like yourself who's prone to mammoth queries 😆
j
I guess I don’t really see the use case for this, in the example you might as well just put the values right in the sql string
hey man this is my first try using SuiteQL, I gotta have a nice juicy SQL query
I found a solution to my LISTAGG problem by building a bitmask, lol
🎉 1
c
do tell!
j
well it’s a bit complicated. I wanted to have a list of all classes of business (one comma separated list per row of my results). Class of business in this case is stored in a custom record that’s linked up to the items.
In my inner SQL I did this: 
POWER(10, (customrecord_subscriber_class.id - 1)) AS bitwise_lob
to convert the ID of the class to a number (e.g. 1, 10, 100, etc)
then in my outer SQL I’m doing this to determine which classes are involved for the grouped row:
Copy code
RTRIM(CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 0, 1) = '1' THEN 'Equipment Breakdown, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 1, 1) = '1' THEN 'Excess, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 2, 1) = '1' THEN 'Crisis Management, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 3, 1) = '1' THEN 'Cyber, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 4, 1) = '1' THEN 'Crime, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 5, 1) = '1' THEN 'Stock & Transit, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 6, 1) = '1' THEN 'CEF, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 7, 1) = '1' THEN 'Env. Liability, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 8, 1) = '1' THEN 'Property, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 9, 1) = '1' THEN 'Mgmt. Liability, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 10, 1) = '1' THEN 'Prof. Liability, ' ELSE '' END
|| CASE WHEN SUBSTR(LPAD(SUM(DISTINCT bitwise_lob) || '', 11, '0'), 11, 1) = '1' THEN 'GL, ' ELSE '' END, ', ') AS lines_of_business,
basically I’ll have something like this ‘000001010101’ or whatever, and that represents which classes should be listed
the things I’ll do when I have no LISTAGG, no WM_CONCAT, and no ARRAY to use in SQL….sigh
c
I'm convinced you like to make mountains to climb when there are none around 🤣
j
I honestly could not figure out another way to do this purely in SQL
c
It's a great solution!
j
It works
and yes I like mountains, see my avatar 😉
s
I hate sql
✔️ 1
e
samesies
m
Circling back to the beginning of the thread, the typical use case for parameterized queries is to avoid sql injection attacks.
a
@michoel That definitely is the strong use case in raw sql, but is sql injection even possible within Netsuite? Does netsuite not sanitize queries?
m
@Azi SuiteQL is read only which considerably minimizes the risk, but there could still be attack vectors if your code interpolates arbitrary strings from untrusted sources into sql
Consider this hack for an example of how its possible to directly execute Oracle functions that NetSuite doesn't officially support by putting a comment on the middle of the formula
a
😱
8 Views
Open in Slack
PreviousNext
Powered by