SuiteQL

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. ...