HANA - 将字符串变量传递到SQL脚本中的WHERE IN()子句
让我们假设脚本计算视图中有一些SQL脚本,它接受单个值输入参数并在另一个计算中为输入参数生成多个输入的字符串视图。HANA - 将字符串变量传递到SQL脚本中的WHERE IN()子句
BEGIN
declare paramStr clob;
params = select foo
from bar
where bar.id = :IP_ID;
select '''' || string_agg(foo, ''', ''') || ''''
into paramStr
from :params;
var_out = select *
from "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"(PLACEHOLDER."$$IP_IDS$$" => :paramStr);
END
这个按预期工作。但是,如果我改变var_out
查询,并尝试使用该变量where子句
BEGIN
...
var_out = select *
from "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"
where "IP_IDS" in(:paramStr);
END
视图将激活,但我仍没有得到查询结果。没有运行时错误,只是一个空的结果集。当我手动将值传递给WHERE IN()
子句时,一切正常。这似乎是一个基本的问题,但我似乎无法让它工作。我甚至使用char(39)
,而不是我的连接表达式''''
,但没有香蕉:(试图
好了,你在这里做的是试图使语句动态。 用于上述条件,你似乎希望一旦您填写paramStr
这将作为一组参数来处理
,该案件不是在所有
让你的榜样去从注释:。paramStr = ' 'ip1','ip2' '
会发生什么,当paramStr
得到填补到你的代码是这样的:
var_out = select *
from "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"
where "IP_IDS" in(' ''ip1'',''ip2'' ');
因此,而不是寻找符合IP_DS = 'ip1' or IP_DS = 'ip2'
的记录,你是从字面上寻找符合IP_DS = ' 'ip1','ip2' '
的记录。
解决此问题的一种方法是使用APPLY_FILTER()
函数。
var_out = select *
from "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW";
filterStr = ' "IP_IDS" in (''ip1'',''ip2'') ';
var_out_filt = APPLY_FILTER(:var_out, :filterStr) ;
前段时间我写过:"On multiple mistakes with IN conditions"。 另外,看看APPLY_FILTER的文档。
工作正常,但现在我在第一次执行时出现了糟糕的性能,当我手动执行相同的查询时,它闪电般快速... – Jenova
SAP注释“2315085 - 在脚本计算视图上使用多值参数查询失败,语法错误不正确”实际上展示了我第一次尝试时失败的APPLY_FILTER()方法。
它还提供了一个UDF_IN_LIST函数,用于将具有项目数组的长varchar字符串从输入参数转换为可用的列表中谓词。
不幸的是,不能让它在SPS11工作修订版111.03尽管有一些可用的参数(SAP注释2457876:出错转换成字符串长度溢出警告) - (距离3)字符串太长例外
然后ALTER SYSTEM ALTER CONFIGURATION('indexserver.ini','System')set('sqlscript','typecheck_procedure_input_param')='false'WITH RECONFIGURE;
-recompile Calc的查看
然后
SELECT * FROM:var_tempout其中OBJECT_ID中(选择 “BWOBJDES” I_LIST。“CROSS_AREA :: UDF_INLIST_P”(:in_objectids, ''));
无效数字异常 - 无效号码
但考虑脚本出来的功能,使得它的工作...
对于这一SPS 11级APPLY_FILTER似乎是唯一的解决方法。而要真正实现它,真的很难说什么是SPS 12的改革。
FUNCTION "BWOBJDES"."CROSS_AREA::UDF_INLIST_P"(str_input nvarchar(5000),
delimiter nvarchar(10))
RETURNS table (I_LIST INTEGER) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
/********* Begin Function Script ************/
BEGIN
DECLARE cnt int;
DECLARE temp_input nvarchar(128);
DECLARE slice NVARCHAR(10) ARRAY;
temp_input := :str_input;
cnt := 1;
WHILE length(temp_input) > 0 DO
if instr(temp_input, delimiter) > 0 then
slice[:cnt] := substr_before(temp_input,delimiter);
temp_input := substr_after(temp_input,delimiter);
cnt := :cnt + 1;
else
slice[:cnt] := temp_input;
break;
end if;
END WHILE;
tab2 = UNNEST(:slice) AS (I_LIST);
return select I_LIST from :tab2;
END;
CREATE PROCEDURE "MY_SCRIPTED_CV/proc"(IN numbers NVARCHAR(5000), OUT
var_out
MY_TABLE_TYPE) language sqlscript sql security definer reads sql data with
result view
"MY_SCRIPTED_CV" as
/********* Begin Procedure Script ************/
BEGIN
-- not working
--var_out = select * from MY_TABLE where NUMBER in (select I_LIST from
--UDF_INLIST_P(:numbers,','));
-- working
DECLARE cnt int;
DECLARE temp_input nvarchar(128);
DECLARE slice NVARCHAR(13) ARRAY;
DECLARE delimiter VARCHAR := ',';
temp_input := replace(:numbers, char(39), '');
cnt := 1;
WHILE length(temp_input) > 0 DO
if instr(temp_input, delimiter) > 0 then
slice[:cnt] := substr_before(temp_input,delimiter);
temp_input := substr_after(temp_input,delimiter);
cnt := :cnt + 1;
else
slice[:cnt] := temp_input;
break;
end if;
END WHILE;
l_numbers = UNNEST(:slice) AS (NUMBER);
var_out=
SELECT *
FROM MAIN AS MA
INNER JOIN l_numbers as LN
ON MAIN.NUMBER = LN.NUMBER
END;
在这种情况下你会得到什么错误信息?另外:您的视图名称中缺少双引号。你想在这里做什么?要么将您的值输入到输入参数或变量中 - 这些不可交换。 –
对不起,我修正了错字。我没有得到任何激活或运行时错误,当我查询脚本calc视图时,我只是没有得到任何结果,但当用'WHERE'SOME_COLUMN'IN('ip1','ip2 ')'它工作正常。就像我说的,像第一个例子一样,使用这个IP实现很好。 – Jenova
并不是说它涉及到这个问题,但我有复杂的服务,HANA优化器不会正确推送过滤器。例如,如果我有一项服务来计算单一贷款编号的余额,那么HANA优化器的工作效果很好,但无法转换输入以传递特定帐号的多个贷款编号。如果没有巨大的性能损失,以图形方式加入此转换不起作用。相反,我将图形视图封装在脚本化的calc视图中,以转换输入并使用多个参数查询原始视图,而不会牺牲性能。 – Jenova