topic Re: HR table join SQL problem in Application Development and Automation Discussions

HR table join SQL problem

emrhkls — Tue, 19 Apr 2016 11:09:31 GMT

Hi all,

I need some help SQL join on HR tables. I want to select SUBTY and/or ICTYP fields from pa0185 table via MERNI field from pa0770 table.

Here is my pa0185 and pa0770 table entries:

... and here is my SQL statement:

TABLES: pa0185, pa0770.

DATA: zpa0185 TYPE TABLE OF pa0185.

SELECT * INTO CORRESPONDING FIELDS OF TABLE zpa0185 FROM pa0185

INNER JOIN pa0770 ON pa0185~pernr = pa0770~pernr

WHERE pa0770~merni EQ '39685669912'.

... and the result is:

I hope to see in SUBTY and ICTYP field as pa0185 table Z1, Z2 or Z9...

How should I change the SQL statement for to get results from pa0185?

Thanks in advance.

Re: HR table join SQL problem

Ankit_Maskara — Tue, 19 Apr 2016 12:00:43 GMT

Search for 'how to use alias' in SQL. You need to use alias to fetch ICTYP from second table. Also only select the required fields and not use.Avoid Select *.

BR.

Re: HR table join SQL problem

Former Member — Tue, 19 Apr 2016 12:10:23 GMT

Hi,

try the following:

Select a~pernr b~subty b~ictyp into itab

FROM pa0185 as b

INNER JOIN pa0770 as a ON pa0185~pernr = pa0770~pernr

WHERE pa0770~merni EQ '39685669912'.

That should bring you the values from table PA0185 instead of PA0770.

Br,

Tim

Re: HR table join SQL problem

emrhkls — Tue, 19 Apr 2016 12:44:10 GMT

Hi Tim,

As you suggested I used statement below and it worked:

SELECT a~pernr

b~subty

b~ictyp INTO CORRESPONDING FIELDS OF TABLE itab FROM pa0185 AS b

INNER JOIN pa0770 AS a ON a~pernr = b~pernr

WHERE a~merni EQ '39685669912'.

Thank you!