SuiteQL Generate Numbers from 1 to N Using DUAL

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.

There are several ways you can attack this approach and I’ll outline each specific approach:

Using `DUAL` and `CONNECT BY`

One approach is to simply create a DUAL view using CONNECT BY with LEVEL that creates an incrementing loop from 1 to n.

This type of query would look something like this:

1
2
3
4
5
6
7
8
9
-- @param hire_date - the hire date in ISO date format (YYYY-MM-DD)
-- @param end_date - (OPTIONAL, empty string permitted) the termination date in ISO date format (YYYY-MM-DD)
-- @param hire_date
SELECT
    TO_CHAR(TO_DATE(?, 'YYYY-MM-DD') + LEVEL - 1, 'YYYY-MM-DD') AS dates
FROM
    DUAL
CONNECT BY
    LEVEL <= NVL(TO_DATE(?, 'YYYY-MM-DD'), CURRENT_DATE) - TO_DATE(?, 'YYYY-MM-DD') + 1

By injecting into the parameters an array of ISO date strings: ['2022-05-14', '2024-09-03', '2022-05-14'] you would achieve a list of all dates from the 14th May 2022 to the 3rd of September (inclusive).

Any further work you would need to do on the result from this query.

But that’s about all you can do with queries involving DUAL and CONNECT BY and LEVEL.

As the DUAL table is a view it doesn’t connect terribly well when using CONNECT BY where the DUAL view is joined with other tables.

Therefore, this type of approach is relatively simple provided the requirement is simple.

Using `DUAL` in a subquery

When you want to do more with the DUAL table you’ll soon find that it isn’t as easy when joining it alongside other tables.

Remember that it is a view and therefore you only want to use it for simple cases, such as generating numbers from 1 to n.

Should you prefer instead to inject an employee id and for the query to fetch the relevant hire and termination dates then the structure of your query will need to change slightly.

Using the same example as above, here’s how your query could look should you just want to insert the internal id of the employee:

 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
34
35
36
37
--- @param employee_id - Netsuite internal id of the employee
WITH inputs AS (
    SELECT
        ? AS employee_id
),
-- Fetch the relevant date values from the employee record
employee_dates AS (
    SELECT
        hiredate AS hire_date,
        -- Use termination date, or current_date if employee is still employed
        NVL(releasedate, CURRENT_DATE) AS end_date,
        -- Calculate the number of days employee has been hired, TRUNC is used to remove decimal
        TRUNC(NVL(releasedate, CURRENT_DATE) - hiredate) + 1 AS days_employed
    FROM
        employee, inputs
    WHERE 
        id = inputs.employee_id
),
-- Fetch the numbers from 0 to 1000
numbers AS (
    SELECT
        a.n + (10 * b.n) + (100 * c.n) AS n
    FROM
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
        employee_dates
    WHERE
        a.n + (10 * b.n) + (100 * c.n) <= employee_dates.days_employed
    ORDER BY
        a.n + (10 * b.n) + (100 * c.n)
)
-- Now to work on the desired output, in this case just the dates is needed
SELECT
    TO_CHAR(employee_dates.hire_date + numbers.n, 'YYYY-MM-DD') AS dt
FROM
    employee_dates, numbers

Obtaining a simple number list from 1 to n can seems quite rudimentary with the CTE numbers, but this works without SuiteQL throwing any errors. It does produce issues in the sense that it is limited to the first 1000 numbers from 0 to 1000, but you could easily add another SELECT clause if you needed to produce 10000 numbers by adding a column d, like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- Fetch the numbers from 0 to 10,000
numbers AS (
    SELECT
        a.n + (10 * b.n) + (100 * c.n) + (1000 * d.n) AS n
    FROM
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
        employee_dates
    WHERE
        a.n + (10 * b.n) + (100 * c.n) + (1000 * d.n) <= employee_dates.days_employed
    ORDER BY
        a.n + (10 * b.n) + (100 * c.n) + (1000 * d.n)
)

Ideally it would be great if SuiteQL could handle the DUAL and CONNECT BY clause within a CTE to handle the generation of numbers, but unfortunately the following code DOES NOT WORK…

1
2
3
4
5
6
7
8
numbers AS (
    SELECT
        LEVEL AS n
    FROM
	    DUAL, employee_dates
    CONNECT BY
	    LEVEL <= employee_dates.days_employed
)

As it produces the following error where the CONNECT BY line is stated in your query…

1
syntax error, state:961(10102) near: BY(25,13, token code:0)

Anyway, to finish off the initial query for this exercise, to calculate the number of working days the employee has worked you can tackle this a number of different ways.

My preference is to have the system count the number of days in the week between the hire date and release date and for the rest of the code to perform any further calculations on the returned data set.

If that’s where you’d like to go to as well then this code will provide that detail:

 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
34
35
36
37
38
39
40
41
42
43
--- @param employee_id - Netsuite internal id of the employee
WITH inputs AS (
    SELECT
        ? AS employee_id
),
-- Fetch the relevant date values from the employee record
employee_dates AS (
    SELECT
        hiredate AS hire_date,
        -- Use termination date, or current_date if employee is still employed
        NVL(releasedate, CURRENT_DATE) AS end_date,
        -- Calculate the number of days employee has been hired, TRUNC is used to remove decimal
        TRUNC(NVL(releasedate, CURRENT_DATE) - hiredate) + 1 AS days_employed
    FROM
        employee, inputs
    WHERE 
        id = inputs.employee_id
),
-- Fetch the numbers from 0 to 1000
numbers AS (
    SELECT
        a.n + (10 * b.n) + (100 * c.n) AS n
    FROM
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
        (SELECT 0 AS n FROM DUAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
        employee_dates
    WHERE
        a.n + (10 * b.n) + (100 * c.n) <= employee_dates.days_employed
    ORDER BY
        a.n + (10 * b.n) + (100 * c.n)
)
-- Now to work on the desired output, in this case just the dates is needed
SELECT
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'MON' THEN 1 ELSE 0 END) AS mondays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'TUE' THEN 1 ELSE 0 END) AS tuesdays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'WED' THEN 1 ELSE 0 END) AS wednesdays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'THU' THEN 1 ELSE 0 END) AS thursdays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'FRI' THEN 1 ELSE 0 END) AS fridays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'SAT' THEN 1 ELSE 0 END) AS saturdays,
    SUM(CASE WHEN TO_CHAR(employee_dates.hire_date + numbers.n, 'DY', 'nls_date_language=english') = 'SUN' THEN 1 ELSE 0 END) AS sundays
FROM
    employee_dates, numbers

The output from this result is a table producing the number of days of the week from the hire date to the termination date:

mondays	tuesdays	wednesdays	thursdays	fridays	saturdays	sundays
121	121	121	120	120	121	121

From this you can then use these counts to do further analysis.

Using DUAL and CONNECT BY#

Using DUAL in a subquery#

Using `DUAL` and `CONNECT BY`

Using `DUAL` in a subquery