Method 1, avoiding any helper tables or functions which are only available in new releases of Teradata:
WITH RECURSIVE NumberRanges(TheNumber,TheString) AS
(
SELECT 1 AS TheNumber,casT(1 as VARCHAR(500)) as TheString
FROM
(
SELECT * FROM (SELECT NULL AS X) X
) DUMMYTABLE
UNION ALL
SELECT
TheNumber + 1 AS TheNumber,
TheString ||',' || TRIM(TheNumber+1)
FROM NumberRanges
WHERE
TheNumber < 10
)
SELECT TheString
FROM NumberRanges
QUALIFY ROW_NUMBER() OVER ( ORDER BY TheNumber DESC) = 1
Method 2, with helper tables and XMLAGG:
SELECT
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rn)|| ',' ) (VARCHAR(255))))
FROM
(SELECT 1 + ROW_NUMBER() OVER (ORDER BY Sys_Calendar."CALENDAR".day_of_calendar) as rn
FROM Sys_Calendar."CALENDAR" QUALIFY rn <= 10) t;
Both methods deliver the string “1,2,3,4,5,6,7,8,9,10”.
Which process do you believe is using fewer Disk IOs and CPU resources?