All Forums Database
sg186048 5 posts Joined 09/12
20 Oct 2015
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
 

dins2k2 51 posts Joined 05/13
24 Nov 2015

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

You must sign in to leave a comment.