outer join

[Former Member]

need to join two tables HRP5103 & HRP5104.

From table HRP5104 I need to take fields (INSTITUTE, START_DATE, END_DATE, SEQNR) and from table HRP5103 (CTC, CURRKEY) on basis of OBJID which is common in both table. I need all the field from HRP5104 & HRP5103 (common & unique)

for e.g. OBJID in HRP5104 are 001,002,003,004,005,007 & in HRP5104 OBJID are 001,002,006 so ineed in joined table as 001,002,003,004,005,006,007.

could you plz provide code

[Former Member]

Hi

Declare a ITAB with the necessary fields

Select

a~INSTITUTE

a~START_DATE

a~ END_DATE

a~SEQNR

b~CTC

b~CURRKEY

into table ITAB

From HRP5104 as a join HRP5103 as b

on a_{objid = b}objid

where a_{plvar = ,,,, and a}objid = ....

(give all the where condition fields)

Reward points if useful

Regards

Anji

[Former Member]

Hi

The best way to get the data for your requirement is to first get data is:

SELECT <fields from respective tables using alias>

INTO CORRESPONDING FIELDS OF TABLE <internal table>

FROM HRP5104 as A LEFT OUTER JOIN HRP5103 as B

ON A_{OBJID = B}OBJID

WHERE <conditions>.

Regards,

Raj

[Former Member]

Hi,

SELECT A_{INSTITUTE A}START_DATE A_{END_DATE A}SEQNR B_{CTC B}CURRKEY INTO CORRESPONDING FIELDS OF TABLE itab

FROM HRP5104 AS A

LEFT OUTER JOIN HRP5103 AS B ON A_{OBJID = B}OBJID.

Regards,

Padmam.

[Former Member]

Hi,

As you need data from both the tables, <b>you will need 2 OUTER JOINs</b>.

Declare a ITAB with the necessary fields.

<b>* This will get 001,002,003,004,005,007.</b>

Select

a~INSTITUTE

a~START_DATE

a~ END_DATE

a~SEQNR

b~CTC

b~CURRKEY

into table ITAB

From HRP5104 as a left outer join HRP5103 as b

on a~objid = b~objid.

<b>* This will get 001,002,006</b>

Select

a~INSTITUTE

a~START_DATE

a~ END_DATE

a~SEQNR

b~CTC

b~CURRKEY

<b>appending</b> table ITAB

From HRP5103 as b left outer join HRP5104 as a

on a_{objid = b}objid.

<b>*This will remove duplicate entries of 001,002

*Hence the final output woulde 001 to 007.</b>

SORT ITAB.

DELETE ADJACENT DUPLICATES FROM ITAB.

This should serve the purpose.