我可以在sql花名占位符中放置sql查询吗 [英] can I place a sql query in a sql hana place holder

问题描述

我可以在 sql hana 占位符中放置sql查询吗?

can I place a sql query in a sql hana place holder?

以下查询有效:

     Select * From table1
     ('PLACEHOLDER' = ('$$IP_ShipmentDate$$', '2020-01-01'))

我正在尝试通过以下内容:

I am trying to pass something like the following:

      Select * From table1
     ('PLACEHOLDER' = ('$$IP_ShipmentDate$$', 
       Select TO_VARCHAR(min("some_date"),'YYYY-MM-DD') from Table2)

当前出现同步错误

推荐答案

错误提示您，列视图参数Can't use column expression as column view parameter不支持表达式.

As error says you, expressions are not supported for column view parameter: Can't use column expression as column view parameter.

但是，有一些计算方法:

However, there are options to calculate it:

• 如果参数的值在语义上与此计算视图相关(例如，该视图需要基于某种逻辑计算的默认值)，则可以使用一个输出参数创建一个存储过程来计算该值，并且使用参数类型源自过程/标量函数.要从前端调整该值，可以将其标记为已启用输入.或者，如果计算值基于用户输入，则可以在 Parameters/Variables->部分中将过程的输入参数映射到计算视图的输入参数.输入参数的过程/标量函数.

• If your parameter's value is semantically related to this calculated view (e.g. you need some default value for this view that is calculated based on some logic), then you can create a stored procedure with one output parameter to calculate the value, and use parameter type Derived From Procedure/Scalar Function. To make that value adjustable from frontend you can mark it as Input enabled. Or if calculated value is based on user input, you can map input parameters of procedure to input parameters of calculation view in section Parameters/Variables -> Procedures/Scalar functions for input parameters.

create procedure sp_dummy (
  in dummy nvarchar(10) default '0',
  out val nvarchar(10)
)
as begin
  val = to_char(current_date, 'yyyymmdd');
end;

• 如果参数的值与计算视图无关，而只是使用计算视图获取该值的一些数据，则可以使用

• If your parameter's value is not related to calculation view and you just use calculation view to get some data for that value, you can use the result set feature of stored procedure/SQLScript block. If your SQL executor does not attempt to parse SQL statement (for example, SAP BO checks for SELECT as the first SQL word after some SP onwards, so this way will not be valid for it) and just gets result set as metadata for column definitions, then you can avoid extra object to wrap calculation logic.

要返回结果集，请使用SELECT语句，因为匿名块没有定义任何参数.

To return a result set, use a SELECT statement because anonymous blocks do not have any parameters defined.

do begin
  declare lv_param nvarchar(100);
  select max('some_date')
    into lv_param
  from dummy /*your_table*/;
  
  select *
  from "_SYS_BIC"."path.to.your.view/CV_TEST" (
    PLACEHOLDER."$$P_DUMMY$$" => :lv_param
  );
end;

基于表RSPCLOGCHAIN的计算视图通过上面声明的过程的输出过滤后得到

That with calculation view based on the table RSPCLOGCHAIN filtered by output of the procedure declared above gives me

LOG_ID                    | DATUM    | INPUT_PARAM | CUR_DATE
--------------------------+----------+-------------+----------
8FRV39X1O8814X6IJMLPI47PV | 20201221 | some_date   | 20201221
C7S9EY3J0GYA76Y2S9CIA39QR | 20201221 | some_date   | 20201221
35R8A3RFUG00EBY8MZQWJLIXF | 20201221 | some_date   | 20201221
850MVHOJIKIDD7EVY6WFIYPZN | 20201221 | some_date   | 20201221

这篇关于我可以在sql花名占位符中放置sql查询吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！