Query to identify missing numbers between 2 values

Database
Teradata Employee

Query to identify missing numbers between 2 values

Hi Teradata experts,

Need your expertise in writing an SQL for the below scenario

I have a  single row in a table range_num  as follows.

start_num

end_num

10

14

Is there any way we can write a query to find all the gaps between 10 and 14 i ..e 11,12,13 in the below format

col1

col2

col3

col4

col5

10

11

12

13

14

Any help would be immense.

Thanks

Tags (1)
1 REPLY
Enthusiast

Re: Query to identify missing numbers between 2 values

Hi, This can be acheived through a SP. Created in TD 14.10.

Replace procedure db_name.createcols()
DYNAMIC RESULT SETS 1
BEGIN

DECLARE stnum INTEGER;
DECLARE endnum INTEGER;
DECLARE i INTEGER;
DECLARE rangeval INTEGER;
DECLARE colconcat VARCHAR(1000);
DECLARE Qry VARCHAR(2000);
DECLARE C1 CURSOR WITH RETURN ONLY FOR S1; --declare cursor for dynamic reseltset

sel a into :stnum from db_name.temp2; --Your input table
sel b into :endnum from db_name.temp2; --Your input table

SET rangeval=endnum-stnum;
SET i=0;
SET colconcat ='Select ';

while (i<rangeval+1) do --finding range(15-10+1=6 values)

set colconcat = colconcat||trim(stnum+i)||' as "col'||trim(i+1)||'", '; --concating all the colums for the given range
set i=i+1;

end while;

SET colconcat=SUBSTR(colconcat, 1, CHARACTER_LENGTH(colconcat)-2)||';';
PREPARE S1 FROM colconcat;
OPEN C1;

END;

Call the above procedure like below and change the yout inpur tablename in the procedure.

call db_name.createcols();

Thanks,

Dinesh