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.
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
Any help would be immense.
Hi, This can be acheived through a SP. Created in TD 14.10.
Replace procedure db_name.createcols()
DYNAMIC RESULT SETS 1
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 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 colconcat=SUBSTR(colconcat, 1, CHARACTER_LENGTH(colconcat)-2)||';';
PREPARE S1 FROM colconcat;
Call the above procedure like below and change the yout inpur tablename in the procedure.