SuiteQL

If you’re using Tim Diedrich’s SuiteQL suitelet in your Netsuite instance to generate a table of results and want to present this result but don’t like the column headers, or if you’re using the result of your SuiteQL query in the rest of your code using the query.asMappedResults() function and want to modify the column names, then here’s a couple of things you can do. Include spaces in column name If you want to make your column header more readable for presentation purposes then the simplest way to do this is to wrap the alias name of the column in double quotes, like so: ...

There are times when I want to generate a list of numbers from 1 to n in Netsuite and it usually involves dates. For example, if I wanted to calculate the number of actual work days a staff member was attending work from their initial hire date to their termination date I like using the DUAL view to help calculate these days. Let’s say Mr. Smith was employed from 14th May 2022 to the 3rd of September 2024 and I need to calculate the number of actual days he was in attendance at work. ...

When creating a SuiteQL query in SuiteScript I’m finding my query starts with the following structure: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 -- @param field_name - explanation of what this is and whether it's optional or not... -- @param date_field - ...and any particular format needed (e.g. YYYY-MM-DD). WITH inputs AS ( SELECT ? AS field_name , TO_DATE(?, "DD/MM/YYYY") AS date_field ), -- If there is more work to be done on the `inputs` or if certain results are needed from -- other tables in Netsuite using these `inputs` then I'll keep daisy chaining the results -- from these common table expressions (CTEs)... table1 AS ( SELECT ... -- when referencing the input fields, use input.student_id (etc) FROM ... -- If it's just one other table I'll use the simpler reference of table_name, inputs; otherwise... CROSS JOIN inputs ) -- Then I'll jump into the body of the query SELECT * FROM x X LEFT JOIN y Y ON -- ... etc CROSS JOIN inputs -- Or whatever the CTE name is from the daisy chaining above WHERE -- If refencing input fields that are *optional* I use the following clauses... ( inputs.field_name IS NULL OR inputs.field_name = 1 -- Or whatever this needs to be ) What I’m finding with my SuiteQL queries is that I want to place all my inputs up at the top. ...

If you’re using SuiteQL to obtain a listing of Customers and their subsequent addresses the following query may be most handy: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT DISTINCT CAEA.addr1 AS addr_line1, CAEA.addr2 AS addr_line2, CAEA.addr3 AS addr_line3, CAEA.state AS state, CAEA.city AS city, CAEA.zip AS zip, CAEA.country AS country, CAB.defaultbilling AS default_billing, CAB.defaultshipping AS default_shipping, CAB.isresidential AS is_residential FROM Customer C LEFT JOIN CustomerAddressBook CAB ON C.id = CAB.entity LEFT JOIN CustomerAddressBookEntityAddress CAEA ON CAB.addressbookaddress = CAEA.nkey WHERE -- ... any conditions on Customer record, i.e. Status is Active, etc This query contains the following details: ...

The LISTAGG is an aggregate function that is a great way to group fields into a string field, usually by concatenating the results of these fields. However, when using this function in SuiteQL syntax, you may come across the oblique error: Search error occurred: Invalid or unsupported search it could be the content of what is being returned from your LISTAGG function exceeds this limit. If you have ever run into this problem using SuiteQL with a query that contains a LISTAGG result, you may be encountering the issue where there are too many characters being rendered in the field that uses the LISTAGG function. ...

I recently ran a budget SuiteQL query that took a whopping 527,138ms to complete (that’s nearly 9 minutes!). I had to take a look and see why it was taking so long, and after a few tweaks found the issue had to do with trying to find the very budget category I needed. So to assist in this discovery I modified my query to search for the budget category first before using it in the rest of my statement. ...