Teradata REGEXP_SPLIT_TO_TABLE输入参数 [英] Teradata REGEXP_SPLIT_TO_TABLE Input Parameter

查看:2157
本文介绍了Teradata REGEXP_SPLIT_TO_TABLE输入参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望能够在CTE中定义输入参数,然后在REGEXP_SPLIT_TO_TABLE函数中使用它,如下所示:

  WITH PARAMS(INPUT_PARAMS)AS 
(SELECT
'?InputParams'AS INPUT_PARAMS

SELECT
TEST_TABLE.SPLIT_PARAMS
FROM
TABLE(REGEXP_SPLIT_TO_TABLE(1,PARAMS.INPUT_PARAMS,'\ |','i')
RETURNS(outkey INTEGER,TOKENNUM INTEGER,SPLIT_PARAMS VARCHAR(8192)CHARACTER SET UNICODE))AS TEST_TABLE
CROSS JOIN PARAMS

但是,这会产生以下错误:


SELECT失败。 [3706]语法错误:
与表操作符或
变量输入参数调用的表函数结合不支持连接表。


将参数放在函数中,如下所示:

  SELECT 
TEST_TABLE.SPLIT_PARAMS
FROM
TABLE(REGEXP_SPLIT_TO_TABLE(1,'?InputParams','\ |','i') -
RETURNS(outkey INTEGER,TOKENNUM INTEGER,SPLIT_PARAMS VARCHAR(8192)CHARACTER SET UNICODE))AS TEST_TABLE

但是我想知道是否有办法做更像上面的第一个例子。

解决方案

这个函数不允许JOIN语法,但是你可以做旧式的连接:

  WITH PARAMS(INPUT_PARAMS)AS 
(SELECT
'?InputParams'AS INPUT_PARAMS

SELECT
TEST_TABLE.SPLIT_PARAMS
FROM
TABLE(REGEXP_SPLIT_TO_T ABLE(1,PARAMS.INPUT_PARAMS,'\ |','i')
RETURNS(outkey INTEGER,TOKENNUM INTEGER,SPLIT_PARAMS VARCHAR(8192)CHARACTER SET UNICODE))AS TEST_TABLE
,PARAMS

事实上,您根本不需要连接:



pre $ WITH PARAMS(INPUT_PARAMS) .SPLIT_PARAMS
FROM
TABLE(REGEXP_SPLIT_TO_TABLE(1,PARAMS.INPUT_PARAMS,'\ |','i')
RETURNS(outkey INTEGER,TOKENNUM INTEGER,SPLIT_PARAMS VARCHAR(8192)CHARACTER SET UNICODE))AS TEST_TABLE

由于您不使用正则表达式,因此您也可以切换到 STRTOK_SPLIT_TO_TABLE

  WITH PARAMS(INPUT_PARAMS)AS 
(SELECT
'?InputParams'AS INPUT_PARAMS

SELECT
TEST_TABLE.SPLIT_PARAMS
FROM
TABLE(StrTok_Split_To_Tab le(1,PARAMS.INPUT_PARAMS,'\ |')
RETURNS(outkey INTEGER,TOKENNUM INTEGER,SPLIT_PARAMS VARCHAR(8192)CHARACTER SET Unicode))AS TEST_TABLE


I'd like to be able to define an input parameter in a CTE and then use it within a REGEXP_SPLIT_TO_TABLE function, like so:

WITH PARAMS (INPUT_PARAMS) AS
(SELECT
    '?InputParams' AS INPUT_PARAMS
)
SELECT
    TEST_TABLE.SPLIT_PARAMS
FROM
    TABLE (REGEXP_SPLIT_TO_TABLE(1, PARAMS.INPUT_PARAMS, '\|', 'i')
        RETURNS (outkey INTEGER, TOKENNUM INTEGER, SPLIT_PARAMS VARCHAR(8192) CHARACTER SET UNICODE)) AS TEST_TABLE
    CROSS JOIN PARAMS

However this yields the following error:

SELECT Failed. [3706] Syntax error: Joined table is not supported in conjuction with table operators or table function invoked with variable input argument.

Putting the parameter right in the function works, as below:

SELECT
    TEST_TABLE.SPLIT_PARAMS
FROM
    TABLE (REGEXP_SPLIT_TO_TABLE(1, '?InputParams', '\|', 'i') --
        RETURNS (outkey INTEGER, TOKENNUM INTEGER, SPLIT_PARAMS VARCHAR(8192) CHARACTER SET UNICODE)) AS TEST_TABLE

But I'm wondering if there's a way to do something more like the first example above.

解决方案

This function doesn't allow JOIN-syntax, but you can do old-style joins:

WITH PARAMS (INPUT_PARAMS) AS
(SELECT
    '?InputParams' AS INPUT_PARAMS
)
SELECT
    TEST_TABLE.SPLIT_PARAMS
FROM
    TABLE (REGEXP_SPLIT_TO_TABLE(1, PARAMS.INPUT_PARAMS, '\|', 'i')
        RETURNS (outkey INTEGER, TOKENNUM INTEGER, SPLIT_PARAMS VARCHAR(8192) CHARACTER SET UNICODE)) AS TEST_TABLE
    ,PARAMS

In fact you don't need the join at all:

WITH PARAMS (INPUT_PARAMS) AS
(SELECT
    '?InputParams' AS INPUT_PARAMS
)
SELECT
    TEST_TABLE.SPLIT_PARAMS
FROM
    TABLE (REGEXP_SPLIT_TO_TABLE(1, PARAMS.INPUT_PARAMS, '\|', 'i')
        RETURNS (outkey INTEGER, TOKENNUM INTEGER, SPLIT_PARAMS VARCHAR(8192) CHARACTER SET UNICODE)) AS TEST_TABLE

And as you don't use a regular expression you might also switch to STRTOK_SPLIT_TO_TABLE:

WITH PARAMS (INPUT_PARAMS) AS
(SELECT
    '?InputParams' AS INPUT_PARAMS
)
SELECT
    TEST_TABLE.SPLIT_PARAMS
FROM
    TABLE (StrTok_Split_To_Table(1, PARAMS.INPUT_PARAMS, '\|')
        RETURNS (outkey INTEGER, TOKENNUM INTEGER, SPLIT_PARAMS VARCHAR(8192) CHARACTER SET Unicode)) AS TEST_TABLE

这篇关于Teradata REGEXP_SPLIT_TO_TABLE输入参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆