如何在Teradata中获取looop等效命令 [英] How to get the looop equivalent command in teradata

问题描述

我对Teradata很陌生.在这里，我看不到任何执行以下命令的循环命令- 输入结构

I am very new to Teradata. Here I dont see any looping command to execute the following-- Input Structure

 A  B  C
 -- -- --
 1  b  c
 1  d  d
 1  c  d
 1 fx  b

Output_Structure-

Output_Structure--

A  B  C
-- -- --
1 fx  b
1  b  c
1  c  d
1  d  d

在这里，如果我们使用CASE结构实现，它将像

here if we implement with the CASE structure it will work, like

CASE
IF (B ='fx' THEN SELECT C AS A1)
CASE
IF(B!= 'fx' THEN SELECT C WHERE B=A1)

但是，如果记录数量更多，那么我们需要通过LOOP来实现.所以对此有任何建议.

but if the number of records are more then we need to implement through LOOP. So any suggestion on this.

推荐答案

这看起来像一个层次结构，为此您需要一些递归查询.

This looks like a hierarchy, you need some recursive query for that.

WITH RECURSIVE cte (i, a,b,c) AS
(
  SELECT 1 AS i,a,b,c 
  FROM tab
  WHERE b = 'fx' -- start with the first value

  UNION ALL

  SELECT cte.i + 1, tab.* 
  FROM tab, cte
  WHERE cte.c = tab.b -- traverse the hierarchy
  AND cte.a = tab.a
  AND cte.b <> cte.c -- stop if b=c
)
SELECT * FROM cte
ORDER BY i

如果存在类似d-b的行，这可能会导致循环，但是如果它超过127(byteint的最大值)，则 1 AS i 将出错.

This might result in a loop if there'a a row like d-b, but 1 AS i will error out if it exceeds 127, the max value of a byteint.

保护无限循环会更加复杂...

Protecting from infinite loop will be more complicated...

要防止无限循环，您需要创建路径，并检查新值是否已存在于现有路径中

To protect from infinite loop you need to create a path and check of the new value is already within the existing path:

WITH RECURSIVE cte (i, PATH, a,b,c) AS
(
  SELECT CAST(1 AS SMALLINT) AS i,
     '.' || CAST(TRIM(b) AS VARCHAR(10000)) || '.',
     a,b,c 
  FROM tab
  WHERE b = 'fx'

  UNION ALL

  SELECT cte.i + 1, 
     cte.PATH || TRIM(tab.b) || '.',
     tab.* 
  FROM tab, cte
  WHERE cte.c = tab.b
  AND cte.a = tab.a
  AND cte.PATH NOT LIKE '%.' || TRIM(tab.b) || '.%'
)
SELECT * FROM cte
ORDER BY 1;

VarChar路径的大小必须足够大以容纳最大的递归级别.

The size of the path VarChar must be large enough to accommodate the largest possible recursion level.

这篇关于如何在Teradata中获取looop等效命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！