I want to generate a list like -3, -4, -5, -6, -7 in Teradata Sql asst. Is there any direct way to do it. Sample attached here.enter image description here
Add a comment
|
1 Answer
In Teradata, there isn't a built-in function for generating a sequence of numbers. However, you can achieve this by using a recursive cte as follows:
WITH RECURSIVE nums AS
(
SELECT -7 AS n FROM DBC.DBCInfoV WHERE InfoKey='Version' -- or from any table you already have
UNION ALL
SELECT n + 1 FROM nums WHERE n < -3
)
SELECT *
FROM nums
ORDER BY n DESC
3 Comments
Andrew
This won't run in teradata. The union requires an actual table. Easy enough to get around with a volatile table though.
Fred
Or just add any FROM / WHERE clause to the initial SELECT that returns a single row. For example
FROM DBC.DBCInfoV WHERE InfoKey='Version'
SelVazi
I have edited my answer, Thank you all For the help