SuiteQL: Generate 1 to N Series With DUAL

To generate rows from 1 to n in SuiteQL, use DUAL with CONNECT BY LEVEL for a simple standalone series. When the upper bound comes from another NetSuite record, a digit-set CTE is more dependable because SuiteQL can reject a correlated CONNECT BY expression.

The shortest working pattern is:

SELECT LEVEL AS n
FROM DUAL
CONNECT BY LEVEL <= ?

With params: [10], this returns the integers 1 through 10.

There are times when I want to generate such a list in NetSuite, and it usually involves dates.

This pattern is useful for filling gaps in reports, producing one row per calendar day, counting weekdays and generating reporting periods even when no transaction exists for a date.

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:

Use DUAL and CONNECT BY for a simple series

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:

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

This is the simplest option when the upper bound can be passed directly as a bind parameter.

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 approach is a good fit provided the requirement is self-contained.

Use a digit-set CTE when the bound comes from a record

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:

--- @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 999
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 number list this way can seem rudimentary, but it avoids the correlated CONNECT BY error. Three digit sets produce 1,000 possible rows with values from 0 through 999. Add a fourth digit set when you need 10,000 possible rows from 0 through 9999:

-- Fetch the numbers from 0 to 9999
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)
)

In this tested query shape, correlating CONNECT BY with employee_dates does not work:

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…

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

Count weekdays across the generated dates

To calculate the number of working days the employee has worked, you can tackle the result in several 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:

--- @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 999
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	120	120	120	121	121

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

Boundaries and practical limits

• CONNECT BY LEVEL <= n generates 1 through n.
• A zero-based digit series must use n < days_employed to generate exactly one row per inclusive employment day when days_employed is already calculated as end_date - hire_date + 1.
• Three digit sets cover at most 1,000 rows (0–999); four cover 10,000 (0–9999).
• Keep the generated range as small as the report requires. Every additional digit set multiplies the intermediate row count by ten.
• CURRENT_DATE follows the active session time zone, which matters near date boundaries.

Related SuiteQL guides

• Filter current, previous and custom date ranges
• Group SuiteQL results by month, quarter and year
• Use EXTRACT(YEAR FROM) and EXTRACT(MONTH FROM)
• Pass dates safely with SuiteQL bind parameters