CODEWITHSUNDEEP
sqlteradata
lea
lea

Reputation: 143

Create Row level data from a range in Teradata

I have a table like below

EMP_NUM START_RNG END_RNG
123 H1 H3
456 H4 H6

and I need it to look like the one below.

EMP_NUM ID
123 H1
123 H2
123 H3
456 H4
456 H5
456 H6

I had asked a similar question in reference to dates in my earlier post, however I don't think EXPAND ON will work since it works on time periods. Is there a way to solve for this in Teradata. I am new to recursion as well. Tried the below logic but getting an error in Teradata. Not sure if alternate syntax exists.

WITH temp AS
(
   SELECT 1 AS ID 
   UNION ALL 
   SELECT t.ID + 1 FROM temp t
   WHERE t.ID < 100000
) -- return table with id from 1 to 100000
SELECT t.ID,  y.EMP_NUM
FROM Table1 y
INNER JOIN temp t ON t.ID BETWEEN y.START_RNG AND y.END_RNG
OPTION (MAXRECURSION 0);

Upvotes: 0

Views: 271

Answers (1)

Fred
Fred

Reputation: 2080

Not sure this is the best answer, but at least it would be syntactically correct.

WITH RECURSIVE temp AS
(
   SELECT * from (SELECT 1 AS ID) z
   UNION ALL 
   SELECT t.ID + 1 FROM temp t
   WHERE t.ID < 100
) -- return table with id from 1 to 100000
SELECT t.ID,  y.EMP_NUM
FROM Table1 y
INNER JOIN temp t ON t.ID BETWEEN y.START_RNG AND y.END_RNG
;

It may be possible to use EXPAND ON by artificially transforming to a date/time range and back, e.g.

SELECT begin(PD)-date'1900-01-01' as ID, EMP_NUM
FROM Table1 
EXPAND ON PERIOD(date'1900-01-01'+START_RNG,date'1900-01-01'+END_RNG+1) as PD;

Upvotes: 2

Related Questions