Creating a single join to select correct HRP1000 language

[l_molenaar]

Hello,

we are trying to get the relationships between an Organisation (O object) and Position (S object) with the correct description of the object (based on language etc).

We have the following select statement:

SELECT o_1000~short AS o_short,
o_1000~stext AS o_stext,
 s_1000~short AS s_short,
 s_1000~stext AS s_stext
 FROM hrp1001 AS o_s_1001
 INNER JOIN hrp1000 AS o_1000
 ON ( o_1000~plvar EQ o_s_1001~plvar
 AND o_1000~otjid EQ o_s_1001~otjid )

 INNER JOIN hrp1000 AS s_1000
 ON ( s_1000~plvar EQ o_s_1001~plvar
 AND s_1000~otjid EQ o_s_1001~varyf )

 INTO TABLE @DATA(lt_o_s_relat)

 WHERE o_s_1001~otype EQ 'O'
 AND o_s_1001~objid EQ '50000125'
 AND o_s_1001~plvar EQ '01'
 AND o_s_1001~rsign EQ 'B'
 AND o_s_1001~relat EQ '003'.

Now I know the language isn't included in here because selecing the language is both dependend on your system langauge, the table T778L and which languages have been entered on the object.

In order to select the correct language based on those requirements we already created a possible select:

SELECT p1000~short, 
 p1000~stext, 
 p1000~langu, 
 CASE p1000~langu
 WHEN @lv_langu THEN '00'
 WHEN t778l~langu THEN t778l~seqen
 ELSE '99'
 END AS seq
 
 UP TO 1 ROWS
 FROM hrp1000 AS p1000

 LEFT OUTER JOIN t778l AS t778l
 ON ( p1000~langu EQ t778l~langu )
 INTO TABLE @DATA(lt_hrp1000)

 WHERE p1000~otype EQ 'SC'
 AND p1000~objid EQ '60001333'
 ORDER BY seq.

Now we want to integrate this into one select statement (so no futher abap code like loops / read table etc.)

Would this be possible and the questions is how is this possible then?

Thanks a lot for your help,

Regards Lukas

[l_molenaar]

Hello Mike,

Thank you for your answer.
For the example i'm using just a random relationship to make the question a bit more visable. In the end we will enhance this select with both relationships / infotypes but also linked IMG tables.

There are three main reasons why we want this:

A: We are on HANA and one of the principles is to have the HANA databse retreive more data than to use the application server.
B: In specific reports we have selections based on the additional data on a relationship in combination with infotypes on objects. In order to capture this we will need to build a Select with miltiple joins

C: Embedded analytics / KPI modeler

So yes for this simple example you could use the function modules / classes you describe. In the long run i'm really looking for a solution in a select statement.

In case of any questions please let me know,

Regards Lukas

[pokrakam]

HR is the one area more than most others where direct table reads need very careful consideration.

Why not use some of the standard functions? FM RH_STRUCT_GET will find related objects. Or RH_STRUC_GET_MULTIPLE_ROOTS to do mass evaluations. You need to supply evaluation paths, configured in OOAW. There are also some newer classes which do similar jobs, e.g. cl_hrbas_pd_object.