topic Re: Reg : Select of Non corresponding data using Join statement in Application Development and Automation Discussions

Reg : Select of Non corresponding data using Join statement

Former Member — Mon, 15 Dec 2008 10:30:40 GMT

Hi all,

I have 2 custom tables. Now i want to select the non corresponding data (Sales Orders) from the this 2 tables. I am using the below code.

SELECT a~mandt

a~account

a~store

a~purch_no

a~dist_id

a~purch_date

a~order_no

a~line_no

a~sched_dt

a~bk_ord_dt

a~last_shp_dt

a~order_qty

a~sched_qty

a~bk_ord_qty

a~shipd_qty

a~uom

a~status

a~shpfrm_loc

a~shpfrm_city

a~shpfrm_reg

a~last_req_id

a~last_req_dt

a~last_req_tm

a~last_resp_id

a~last_resp_dt

a~last_resp_tm

a~mod_user

FROM ztab1 AS a

INNER JOIN ztab2 AS b

ON a_{order_no NE b}order_no

INTO TABLE it_eorditmstat.

ztab1 have 27 records. ztab2 have 313. I am getting 8345 records into table it_eorditmstat which should not . Please help me to overcome this scenario.

Thanks in advance.

Regards,

Srinivas

Re: Reg : Select of Non corresponding data using Join statement

Former Member — Mon, 15 Dec 2008 10:43:48 GMT

In case of inner JOIN your are getting the result as A X B.

What do you mean by non corresponding data?

Clarify your requirement please.

Re: Reg : Select of Non corresponding data using Join statement

Former Member — Mon, 15 Dec 2008 10:58:44 GMT

Hi,

Thanks for your reply.

Non corresponding means I want Not matching records between both the tables.

For example.

I have an Order 100004550 in table ZTAB1 if this order is not available in ZTAB2 then record should be selected in the final internal table.

Please reply for any clarification.

Thanks in advance.

Re: Reg : Select of Non corresponding data using Join statement

Former Member — Mon, 15 Dec 2008 11:11:56 GMT

Hi ,

Using inner join will affect Performance and can cause issues in Production .

Try to use Views instead of inner join and select data accordingly

Use FOR ALL ENTRIES .

First select all data from one DBTAble into Internal table and then select corresponding entries as per your KEY

Select*from DBtable FOR ALL ENTRIES IN <itab> WHERE field <> itab-field .

Re: Reg : Select of Non corresponding data using Join statement

Former Member — Mon, 15 Dec 2008 11:13:48 GMT

Try this approach if possible.


SELECT data from both tables independently.

LOOP AT it_ztab1. 
READ it_ztab2. 
IF sy-subrc <> 0. 
APPEND it_eorditmstat. 
ENDIF.
ENDLOOP.