Implementation of while loop in TD sql

Database

Implementation of while loop in TD sql

Hi All,
I am converting a oracle procedure. There is a while loop being used inside. I need to implement while loop using TD sql. could you please give some hints on how to implement while loop using TD SQLs.

Thanks in advance.

Regards
Chandrashekar
4 REPLIES
Enthusiast

Re: Implementation of while loop in TD sql

have a look at the WITH RECURSIVE clause

Re: Implementation of while loop in TD sql

Hi,
Thanks for the suggestion, I am able implement while loop using WITH RECURSIVE. But I am facing problem in inserting records into temp table from this.

I have created a temp table:

CREATE VOLATILE TABLE INV_REF_TEMP
(
INVENTORY_CODE_ID DECIMAL(15),
PARENT_INVENTORY_CODE_ID DECIMAL(15),
PRIORITY DECIMAL(5),
ROOT_LEVEL DECIMAL(5),
ULTIMATE_PAR_ID DECIMAL (15),
ULTIMATE_PAR_PRIORITY DECIMAL (5),
STATION_ID DECIMAL (15)
)

Now I want to insert rec from WITH RECURSIVE clause into INV_REF_TEMP table. I am using beloq query:

INSERT INTO INV_REF_TEMP
(
INVENTORY_CODE_ID,
PARENT_INVENTORY_CODE_ID ,
PRIORITY ,
ROOT_LEVEL ,
ULTIMATE_PAR_ID ,
ULTIMATE_PAR_PRIORITY ,
STATION_ID
)
With Recursive TEMP_TABLE (
inventory_code_id,
parent_inventory_code_id,
PRIORITY,
station_id,
root_lvl) as
(
select
root.inventory_code_id,
root.parent_inventory_code_id,
root.PRIORITY,
root.stationid,
1 as root_lvl
from SANDBOX.INVENTORY_CODE Root
where priority > 30
and parent_inventory_code_id IS NOT NULL
and parent_inventory_code_id <> inventory_code_id
UNION ALL
select
Indirect.inventory_code_id,
Indirect.parent_inventory_code_id,
Indirect.PRIORITY,
Direct.station_id,
Direct.root_lvl + 1 Indirect_lvl
from TEMP_TABLE Direct , SANDBOX.INVENTORY_CODE Indirect
where Direct.parent_inventory_code_id = Indirect.inventory_code_id
and Indirect.parent_inventory_code_id <> Indirect.inventory_code_id
and Indirect.Priority <= 30
)
select distinct
TEMP_TABLE.inventory_code_id as inventory_code_id_1
, TEMP_TABLE.parent_inventory_code_id as parent_inventory_code_id_1
, TEMP_TABLE.PRIORITY priority_1
, TEMP_TABLE.root_lvl root_lvl
, B.INVENTORY_CODE_ID inventory_code_id_2
, B.PRIORITY priority_2
, TEMP_TABLE.station_id station_id_1
from TEMP_TABLE JOIN SANDBOX.INVENTORY_CODE B
ON B.inventory_code_id = TEMP_TABLE.parent_inventory_code_id
WHERE B.Priority <= 30;

It says rows processed but in INV_REF_TEMP table no rows inserted.

Please give hints on how to insert into a table from WITH RECURSIVE clause

Thanks
Chandru
Junior Contributor

Re: Implementation of while loop in TD sql

Hi Chandru,
if you SHOW TABLE INV_REF_TEMP you'll see ON COMMIT DELETE ROWS.

Just change it to COMMIT PRESERVE ROWS.

Btw, in Teradata there's a LOOP, too, you just have to write it within a Stored Procedure.

Dieter
Enthusiast

Re: Implementation of while loop in TD sql

Hi,

Could someone send the sample stored proc using loop and con currency controls e.g. activitycount , errorcode check and GOTO statements.

Thanks,