Teradata REGEXP_SPLIT_TO_TABLE输入参数 [英] Teradata REGEXP_SPLIT_TO_TABLE Input Parameter
问题描述
我希望能够在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屋!