cds view join和association

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1：创建两张表：ztt_teacher01 和ztt_teacher02 用于 cds view中的join和association

2：创建两个cds view：ztt_teacher01_id_name和 ztt_teacher02_id_salary

3：在ztt_teacher01_id_name 中使用连接对数据表进行查询

@AbapCatalog.sqlViewName: 'TEACHER01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'teacher table have id,name field'
define view ztt_teacher01_id_name as select from ztt_teacher01 as t01 left outer join ztt_teacher02 as t02 on t01.id = t01.id {
  t01.id,
  name,
  salary
}

结果如下：

4： ztt_teacher02_id_salary

@AbapCatalog.sqlViewName: 'TEACHER02'   //名字，改名字用于外部交互。
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'teacher table has id_salary'
define view ztt_teacher02_id_salary as select from ztt_teacher02 {
  id,
  salary
}

5: ztt_teacher01_id_name 使用association

@AbapCatalog.sqlViewName: 'TEACHER01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'teacher table have id,name field'
define view ztt_teacher01_id_name as select from ztt_teacher01 as t01
  association [..] to teacher02 as asso02 on $projection.id = asso02.id {
  t01.id,
  name,
  asso02.salary
}

结果如下：

因为association 涉及到两个cds view，因此不能使用测试环境对ztt_teacher01_id_name 进行mock 数据。但是可以对cds view 进行数据库查询。步骤如下：

1：将cds view ztt_teacher01_id_name 中的view 定义改为：

@AbapCatalog.sqlViewName: 'TEACHER01'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'teacher table have id,name field'
define view ztt_teacher01_id_name as select from ztt_teacher01 as t01
  association [..] to teacher02 as asso02 on $projection.id = asso02.id {
  t01.id,
  name,
  asso02.salary,
  asso02          //将另一个cds view 作为 ztt_teacher01_id_name 的一个属性。
}

2：定义查询语句，对teacher02 中的字段salary进行查询：

  method GET_TEACHER_SALARY_BY_ID.
    SELECT SINGLE \asso02-salary  FROM ztt_teacher01_id_name
      WHERE ztt_teacher01_id_name~id = @iv_id
      INTO @ev_salary.
  endmethod.

3： unit test 调用查询语句。

    METHOD Test_teacher_salary_assocation.

    TYPES: BEGIN OF teacher_association,
           id TYPE ztt_teacher01-id,
           name TYPE ztt_teacher01-name,
           salary TYPE ztt_teacher02-salary,
      end of teacher_association.

    data lt_TEST_DATA TYPE STANDARD TABLE OF teacher_association.
    .
    DATA lv_salary TYPE i.

*    lt_TEST_DATA = VALUE #(  " 对association不能插入假数据了，因为底层有两个 view。而创建的 test_environment只是对一个view进行虚拟。
*                           ( id = '1' name = 'lisi' salary = 10 )  " the first data adds to cds view
*                           ( id = '2' name = 'zhaoliu' salary = 20 )  " other data with same name will be abandoned.
*                           ).
*    lv_cds_test_environment->insert_test_data( i_data = lt_TEST_DATA ).

    CALL METHOD f_cut->GET_TEACHER_SALARY_BY_ID(  "调用方法，直接对cds view进行查询，该cds view 直接查询数据库中的数据。
      EXPORTING
        iv_id   = lv_id
      IMPORTING
        ev_salary = lv_salary ).
    cl_abap_unit_assert=>assert_equals( act = lv_salary
      exp =
      msg = 'the name is not expected!' ).
  ENDMETHOD.

成功执行。