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.

I have found using the string interpolation character ? dotted throughout my query both annoying to locate, and difficult to craft when creating the array of strings as the parameter. Once you have more than 3 parameters it gets quite painful to manage and you’ll need to create a function to help identify what parameters are needed for your query.

By defining all the query strings up the top it helps to read the parameter documentation (as it’s just above it) and to then reference throughout the rest of your queries.

Handling optional parameters

If you have optional parameters then you need to be careful of your operations on those NULL parameter values.

You should check if the parameter IS NULL before doing much with the parameter value.

For example, some queries may want to specifically operate on a date or a specific department or subsidiary. As these inputs are optional I need to design my queries to compensate for NULL values. Therefore, you’ll see my query contain in their WHERE clauses something like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

WHERE
    (
        inputs.some_optional_field IS NULL
        OR inputs.some_optional_field = 'T' -- Or whatever
    )
    AND (
        inputs.some_other_optional_field IS NULL
        OR inputs.some_other_optional_field = 'F' -- Or whatever
    )
    -- ... etc for all optional fields used in the filtering of a query

So far this type of structure, while more verbose, has helped to show better structure with my SuiteQL queries. It’s like I’m using OOP principles when writing my queries!

This structure may certainly change in the future, but it’s what has helped me to date.