Solved: AMDP exporting table with fields from various sour...

Former Member · ‎2014 Apr 11

I am writing an AMDP that has an output table a list of employees and attributes. These attributes have various sources with various keys. If I cannot with any practicality construct this table with a single select statement, which given my search criteria, I probably can't, must I break my output table into multiple output tables that can be created with a single select?

I declare the structure of output table et_employees in the class as something like

BEGIN OF ty_employee,

emp_id(12) TYPE c,

emp_name(80) TYPE c,

org_unit(8) TYPE c,

region(5) TYPE c,

country(3) TYPE c,

jb_prf_as TYPE c,

sol_gr_as TYPE c,

snippet(1000) TYPE c,

score TYPE integer,

END OF ty_employee,

tt_employes type table of ty_employee.

As far as I can tell, I cannot do an update et_employees in the method to modify individual fields. I can only do the select statement.

I see my choices as

elaborate select statement that I may or may not be able to construct (haven't thought this through but it may be doable)
more than one output table
create multiple local tables and then do a join on them for the output table.

I am seeing the last one as my best option.

To throw in an unrelated issue, AMDP procedure cannot seem to cope with a table that begins with a slash, e.g., /MRSS/D_SQP_ESTR. The USING statement is o.k. but any access in a select or inner join gets an error.

jasmin_gruschke · ‎2014 Apr 13

Hi Deborah,

let me do some assumptions on your problem and then try to help you:

Assumption A: You only like to query data from tables, which, I simply assume, are available in the ABAP data dictionary. In this case, I don't think there is a performance gain with AMDPs compared to OpenSQL, so just use OpenSQL and do joins on the relevant tables, leading to exaclty one resultset in the output.

Assumption B: You need the AMDP because you have a good reason and you like to query data from tables employee_source_a and employee_source_b for your resultset. In case yes, you can e.g. use "temporary" tables (don't declare them explicitly), e.g. use the construct like:
lt_employee_source_a = select ...from employee_source_a ...;

lt_employee_source_b = select ... from employee _source_b...;
et_employee = select ... from :lt_employee_source_a ... (inner/left outer) join lt_employee_source_b on...;

Or you could use the CE_JOIN function if that suits your SQLScript development better.

To elaborate a query statement for et_employee for without the lt_xxx tables is hard to say from your question - it should be posible if there are no nasty aggregations/calculations which prevent it :-).

There's no need to use two resultsets in the output in case that's not what you need in the application.

The option to have several resultsets is rather a feature of DB Procedures, which allow for several resultsets while a view/OpenSQL query can only give you one resultset.

Conclusion: You answered your question yourself, the last option seems to be the best option :-).

Concerning the "slash issue": Guessing around I'd propose to use quote the table name like "/MRSS/D_SQP_ESTR"... but just a guess. Could you please post the error message or open a second discussion on the issue?

Cheers,

Jasmin

former_member185511 · ‎2014 Apr 12

Hi,

My suggestions is dont rwite your statements in AMDP method, just create a HANA procedure containing local temporary tables to process your data how you want (insert update do what ever you want) and export your result tables.

Later on AMDP just call this procedures and get the results tables to your output parameters.

Former Member · ‎2014 Apr 16

I don't see how that solves the problem.

I still have to write the procedure with the results complexity. The language is the same, . It also creates an object that won't get automatically transported with the ABAP objects. I would have to do a snapshot in an SAP HANA Transport Container to move the separate procedure.

jasmin_gruschke · ‎2014 Apr 13