re: Help converting SQL Server Loop Query to Teradata!

Database
Fan

re: Help converting SQL Server Loop Query to Teradata!

Hi, I'm new to Teradata but am in the process of converting all of my SQL to run on my new Teradata App db....

I have a SQL snippet loop query that runs on SQL Server but it will not run as is on Teradata. I think I need to do something with cursor but am unsure. Can someone tell me how to convert this statement to run on Teradata? Thanks in advance.

CD1:

UPDATE NFAA_TABLES.NFAA_REGIONCD
SET LoopSpan = 'A'
from NFAA_TABLES.NFAA_REGIONCD nf1
WHERE LoopSpan is null
and exists
(
select *
from NFAA_TABLES.NFAA_REGIONCD nf2
WHERE nf2.LoopSpan = 'A'
and nf2.ALocClliCd = nf1.ZLocClliCd
and nf2.azcac = nf1.azcac
)

if @@rowcount > 0 goto CD1;
Tags (3)
4 REPLIES
Fan

Re: re: Help converting SQL Server Loop Query to Teradata!

Any ideas out therer?
Enthusiast

Re: re: Help converting SQL Server Loop Query to Teradata!

You need to use a recursive query to determine the hierarchy in your Region_Cd table.

The following will work:

Create Volatile Table Region_CD
(azcac Smallint Not Null,
ALocClliCd Char(3) Not Null,
ZLocClliCd Char(3),
LoopSpan Char)
Unique Primary Index (ALocClliCd)
On Commit Preserve Rows
;

Insert Into Region_Cd Values (1,'A',Null,Null);
Insert Into Region_Cd Values (2,'B',Null,Null);
Insert Into Region_Cd Values (1,'AA','A',Null);
Insert Into Region_Cd Values (1,'AB','A',Null);
Insert Into Region_Cd Values (1,'AAA','AA',Null);
Insert Into Region_Cd Values (2,'BA','B',Null);

Sel * From Region_Cd
Order By 1,2,3;

Create Recursive View Region_Hier
( azcac, ALocClliCd, ZLocClliCd, LoopSpan)
AS
(Select Root.azcac
, Root.ALocClliCd
, Root.ZLocClliCd
, LoopSpan
From Region_Cd Root
Where Root.ZLocClliCd is Null
UNION ALL
Select LwrLvl.azcac
, LwrLvl.ALocClliCd
, LwrLvl.ZLocClliCd
, 'A'
From Region_Hier BaseL,
Region_Cd LwrLvl
Where BaseL.azcac = LwrLvl.azcac
And BaseL.ALocClliCd = LwrLvl.ZLocClliCd
)
;

Update Region_Cd
Set LoopSpan = Region_Hier.LoopSpan
Where Region_Cd.LoopSpan is Null
And Region_Cd.azcac = Region_Hier.azcac
And Region_Cd.ALocClliCd = Region_Hier.ALocClliCd
And Region_Cd.ZLocClliCd = Region_Hier.ZLocClliCd
;

Select * From Region_Cd
Order By 1,2,3
;

-------------------------------------------------
You have to create the view because you cannot use a "With Recursive" clause in a derived table - and you need the derived table to do it in the update statement. But you can use a Recursive view in an update. (Never really understood why the difference, but ce la vie.)

If the hierarchy changed, but LoopSpan was left set on a record which now was a perent record, you will need a similar piece of code to clear LoopSpan.
I would use an alternative view and just use the Recursive View to determine Loopspan; remove the column from the base table. It reduces update overhead on a refresh and allows you to extend - eg you can easily add ultimate parent.
Fan

Re: re: Help converting SQL Server Loop Query to Teradata!

Jimm - thank you very much. Question...The Region_CD Table is already created and has ~26 million records. So, I'm confused as to why I need to create a new Region_CD table? I think I understand why I need to create the view, but why the table? - i.e.

Create Volatile Table Region_CD

I guess I'm trying to understand how the SP works. The table in question that I am updating is NFAA_TABLES.NFAA_REGIONCD.

Thanks,

Matthew

Enthusiast

Re: re: Help converting SQL Server Loop Query to Teradata!

It may exist on your saystem; it did not on mine! I simply set up a table for testing and put a few records in it to test.
As for the database/ tablenames, substitute your names accordingly.