topic outer join in Application Development and Automation Discussions

outer join

Former Member — Thu, 24 May 2007 06:12:39 GMT

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

Re: outer join

Former Member — Thu, 24 May 2007 06:19:58 GMT

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

Re: outer join

Former Member — Thu, 24 May 2007 06:20:25 GMT

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

Re: outer join

Former Member — Thu, 24 May 2007 06:28:27 GMT

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.

Re: outer join

Former Member — Thu, 24 May 2007 06:31:12 GMT

Hi,

As you need data from both the tables, you will need 2 OUTER JOINs.

Declare a ITAB with the necessary fields.

* This will get 001,002,003,004,005,007.

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.

* This will get 001,002,006

Select

a~INSTITUTE

a~START_DATE

a~ END_DATE

a~SEQNR

b~CTC

b~CURRKEY

appending table ITAB

From HRP5103 as b left outer join HRP5104 as a

on a_{objid = b}objid.

*This will remove duplicate entries of 001,002

*Hence the final output woulde 001 to 007.

SORT ITAB.

DELETE ADJACENT DUPLICATES FROM ITAB.

This should serve the purpose.